Running Total in Power BI for non date Values | How to calculate running total in Power BI using DAX

No video

Running Total in Power BI for non date Values | How to calculate running total in Power BI using DAX

Рет қаралды 4,349

Күн бұрын

#dax #powerbi_training #powerbidax
We learn how to calculate running total in Power BI for non date values using DAX.In the earlier video we found out the process to create a running total for date values.So we have two options for the running total :
1.Option 1: Using sort on country column.Here we apply the below logic in DAX and create a measure as shown :
RT for Country =
CALCULATE([Sum_Sales],
FILTER(ALL(DimGeography[EnglishCountryRegionName]),
DimGeography[EnglishCountryRegionName]less than =max(DimGeography[EnglishCountryRegionName])
))
2.option 2: Using sort on total sales as shown below .To create the running total we use the below DAX measure:
RT for Sales_Amount =
var current_Rank=RANKX(ALL(DimGeography[EnglishCountryRegionName]),
[Sum_Sales],,DESC,Dense)
var Result=CALCULATE([Sum_Sales],
FILTER(ALL(DimGeography[EnglishCountryRegionName]),
RANKX(ALL(DimGeography[EnglishCountryRegionName]),
[Sum_Sales],,DESC,Dense) less than=current_Rank)
)
return Result
This is a really useful feature and one that you should definitely learn how to use!
Check out other Power BI Tutorials Videos :
1.SamePeriodLastYear with Example: • difference between par...
2.How to Calculate Running Totals: • How to calculate runni...
3.Duplicate Vs Reference : • power bi difference be...
4.Merge Vs Append: • power bi difference be...
5.Calculate Function in DAX Power BI: • What is Calculate Func...
6.Filter Function in Power BI: • Filter Function in Pow...
7.Row Vs Filter Context: • Row Context Vs Filter ...
8.ALL DAX Function: • How to use ALL Dax Fun...
9.ALL Vs ALLSELECTED : • allselected dax|all vs...
10.ALL Vs ALLSELECTED VS ALLEXCEPT : • All Vs AllSelected Vs ...
11.ALL Vs REMOVEFILTERS : • ALL Vs REMOVEFILTERS D...
12.TOPN Dax Usage : • TOPN Dax | How to use ...
13.Summarize Dax Function in Power BI: • summarize dax | How to...
14.Summarize Vs Summarizecolumn : • summarize vs summarize...
15.Calendar Vs CalendarAuto : • Calendar Vs Calendar A...
16.How to create a date table in Power BI : • how to create a date t...
17.How to calculate Running total for date values in Power BI
• How to calculate Runni...
Learn Power BI through our vlog and Free Videos:
powerbizone.co...
You can download the pbix file for your self practice sessions from :
drive.google.c...
Do not forget to Like ,Subscribe and comment which keeps me motivated !

Пікірлер: 29

@powerbizone Жыл бұрын

You can download the pbix file for your self practice sessions from : drive.google.com/file/d/1AeNZ7RQaVb2pGZpGt0WeHdI_V9nQK4DE/view?usp=sharing Do not forget to Like ,Subscribe and comment which keeps me motivated !

@user-rz5su6xd8p 8 ай бұрын

Thank you so much! you are a real lifesaver!

@powerbizone 8 ай бұрын

thnx :)

@asaravana3966 Ай бұрын

Thank you sir it helps me a lot I struggle for 1week

@powerbizone Ай бұрын

Glad it helped you dear:) ..Keep practising & plz dont forget to subscribe !

@amitshukla189 Жыл бұрын

The way you explain logic is outstanding.

@powerbizone 11 ай бұрын

thanks

@amritwadhwa 3 ай бұрын

Very good video. Thanks for explaining this so clearly. Is there a way to get rid of '1' that appears on the column subtotal?

@richardsaldana4880 3 ай бұрын

I have a similar goal to the tutorial you provided; however, in the case if you were to filter out a country like united states, I would like the cumulative total to start at the next highest country. Could you provide insight to how that can be done? Many thanks!

@faisalag9611 Жыл бұрын

Awesome. Thanks for sharing.

@powerbizone Жыл бұрын

My pleasure :)If you like the video don't forget to subscribe to our channel.

@akankshatripathi8048 10 ай бұрын

How to do running total if the measure value (Sales in your case) is repeating. Dense Rank will give repeated values.

@powerbizone 8 ай бұрын

Thanks for the comment Akanksha ..Why would the sales for multiple months be same :).Just a thought .This should work for any numbers actually .Do let us know if its not .Also do refer to the below arrticle community.fabric.microsoft.com/t5/Desktop/Last-12-Months-of-Running-Total-Measure/td-p/2034692

@siewleesiewlee3638 10 ай бұрын

why the grand total value is not equal to total 2.93m

@powerbizone 8 ай бұрын

Thank you for your comment.Sales Amount is a column while rank and RT for sales are measures .The total would appear perectly for any inbuilt column while logic differs when measures are in place .You can simply turn off the total

@mohamedmubeen2913 Жыл бұрын

Failed to save modifications to the server. Error returned: 'The following system error occurred: Class not registered '. I got this error while loading data, what can I do?

@powerbizone Жыл бұрын

Thank you for letting us know. We will reupload and let you know once done.

@powerbizone Жыл бұрын

Bro..try now drive.google.com/file/d/1AeNZ7RQaVb2pGZpGt0WeHdI_V9nQK4DE/view?usp=share_link

@mohamedmubeen2913 Жыл бұрын

@@powerbizone Thank you so much for your responses bro, I fix the issue

@powerbizone Жыл бұрын

@@mohamedmubeen2913 Welcome bro and if you like the content ,do subscribe !

@mohamedmubeen2913 Жыл бұрын

@@powerbizone Sure👍

@sharonrubavathy5812 Жыл бұрын

I am trying to do running total on non date column. I am trying to show it in a table visual. I have 18k rows. Following the dax, its working for me but takes some 20 minutes for loading the table visual. Can you help me in optimizing the measure or any other way to achieve running total faster. Not sure, why just for 18k rows of data, its taking too long to load the running sum measure. I am attaching the error and sample file below. Please help me out. If u drag the running sum measure into table visual, its taking too long to load drive.google.com/file/d/16B_PL6pYo4HmsyCILRmXJL0WIXt_NYsa/view?usp=share_link

@powerbizone Жыл бұрын

Just at initial thoughts ,you are calling one measure from inside another .Also once you use ALLSELECTED inside RANKX its bound to take time since it will calculate from beginning till that row "for each row".Bro,Did you try troubleshooting via performance analyzer ?

@sharonrubavathy5812 Жыл бұрын

@@powerbizone no I didn't know to use performance analyzer. Can you please provide me with a optimized code. Please help me in this issue. Been struck in this real time project for past 2 weeks.

@powerbizone Жыл бұрын

@@sharonrubavathy5812 Bro its late night in my Country ..Will try your code tomorrow and let you know . In mean while try community.powerbi.com/t5/Desktop/Running-Total-on-a-non-date-column/m-p/44470

@sharonrubavathy5812 Жыл бұрын

@@powerbizone yeah sure no issues. Please do try tomorrow and help me. Grateful to you

@sharonrubavathy5812 Жыл бұрын

@@powerbizone hi. Did you try on the measure brother. I couldn't resolve it . If u have any suggestions, please let me know