Forum Discussion
Problems on implementing RunningSum measure using DAX
Hi mfattibello,
I'm assuming you have another column on your table that identifies the ranking and not only based on the value.
Try this measure and see if it works:
CUMULATIVE =
VAR Sum_Revenue =
SUM ( Ranking[Revenue] )
RETURN
IF (
NOT ( ISBLANK ( Sum_Revenue ) ),
CALCULATE ( Sum_Revenue ),
FILTER ( ALL ( Ranking[TYPE] ), SUM ( Ranking[Revenue] ) <= Sum_Revenue )
)As you can see in my formula I assumed you have a TYPE column in wich you make the ranking work.
In this table the Ranking is made using the revenue column
Ranking = RANKX(ALL(Ranking[Revenue]),CALCULATE(SUM(Ranking[Revenue])))
However to make the cumulative total I have to use another column (TYPE).
Regards,
MFelix
Hello, MFelix and v-yulgu-msft!Thanks for yours explanations. On my scenario, I don´t have a specific column at my table to ranking be identified. The user chooses some options at the slicer. So, according with these selections, my ranking measure is calculated based on the revenue values.
Somehow, my ranking measure is a little "crazy". As you can see at the image, the position '2' dissapeared. The measure with red borders is my revenue (divided per 1000), and the Running Sum calculation is doing correctly until the RK '5', where the calculation gets wrong.
BTW, I had to transcript the results into a Excel grid, because I cannot paste the image here :(RK Running Sum Revenue 1 316,79 316,8 3 457,83 141 4 497,9 40,1 5 212,77 31,7 6 559,56 30 7 251,79 9 8 258,58 6,8
Best regards,Marcos Fattibello
Hi,
Could you share the link from where i can download your workbook. Please remove information which is not relevnt for the question.
Hi mfattibello,
On my scenario, I don´t have a specific column at my table to ranking be identified. The user chooses some options at the slicer. So, according with these selections, my ranking measure is calculated based on the revenue values.
Didn't my original suggestion meet your requirement? In my original post, the rank value and running total value will be changed dynamically based on slicer selection.
Would you please provide some sample data (detailed data in source table)? And provide the measures you used currently.
Regards,
Yuliana GuHello, v-yulgu-msft and Ashish_Mathur!
Thanks for your attention. I think it´ll be easier I explain what I exactly need to do. On the reports that I´m developing, I´ll have a share of the revenue and a running sum of this share. For the share, I easily found the native option of the Power BI to show the values as "Percent Of The Grand Total". So, I simply create a new measure for revenue, renamed it and choosed this option to show the share values.
Following the same logic, I was trying to creating the rank and the running sum to achieve a 'running percent of the grand total'. Even the running metric, on my case, gets correct until the half of the rows, if I choose this same option for show values as percent, this share gets wrong, beggining from 100%, once that it´s considered the total as the first row value.
Unfortunatelly, I cannot send the structure of data that I´m using, but I put on an Excel file a very filtered data I had and the measure that I need to implement. I think this can help you. If you need more information, I´ll sending the way I can share...
*%GT SHARE What I Need to Do CATEGORY REVENUE 0,550583645 0,550583645 A 316,8 0,245128689 0,795712334 B 141,0 0,069646772 0,865359105 C 40,1 0,055029687 0,920388792 D 31,7 0,052140249 0,972529041 E 30,0 0,015676835 0,988205876 F 9,0 0,011794124 1 G 6,8 Total 575,4 *This value is calculated nativelly on PBI using the show value as "Percent Of Grand Total", using the Revenue metric. (The Excel file, reproduced above, is located on the following link: https://we.tl/ntZSybNJvs)
Best regards,
Marcos
