Forum Discussion
Problems on implementing RunningSum measure using DAX
Hello, 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
Hi,
I must first of all mention that my solution throws incorrects results when a filter/slicer is applied but i am still sharing it because someone else may be able to pitch in/make corrections
Total revenue
=SUM([REVENUE])
Rank
=if(HASONEVALUE(Data[CATEGORY]),RANKX(ALL(Data[CATEGORY]),[Total revenue]),BLANK())
Contribution
=[Total revenue]/CALCULATE([Total revenue],ALL(Data[CATEGORY]))
Cumulative revenue
=SUMX(TOPN([Rank],CALCULATETABLE(VALUES(Data[CATEGORY]),all(Data[CATEGORY])),[Total revenue]),[Total revenue])
Cumulative contribution (%)
=[Cumulative revenue]/SUMX(ALL(Data[CATEGORY]),[Total revenue])
Here is a screenshot