Forum Discussion

mfattibello's avatar
mfattibello
Regular Visitor
8 years ago

Problems on implementing RunningSum measure using DAX

Dear,     I´m facing problems on implementing RunningSum measure using DAX. I have a rank measure showing the best revenues on a determined period and another dimension´s elements, according with ...
Need Help
Tutorial Requests
v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

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 Gu

mfattibello's avatar
mfattibello
Regular Visitor
8 years ago

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 SHAREWhat I Need to DoCATEGORYREVENUE   
0,5505836450,550583645A316,8   
0,2451286890,795712334B141,0   
0,0696467720,865359105C40,1   
0,0550296870,920388792D31,7   
0,0521402490,972529041E30,0   
0,0156768350,988205876F9,0   
0,0117941241G6,8   
       
       
  Total575,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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    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