Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mfattibello
Regular Visitor

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 my slicers. I´m trying to create a revenue running sum measure based on this rank, so the revenue would be cummulative until the last value of this rank. I cannot create the rank logic as a column, because my table has hundreds and hundreds of rows. Below, a schema about what I need to do.

Can anyone help me, please?

 

RKRevenueRunningSum
1900000900000
28000001700000
37000002400000
46000003000000
55000003500000
64000003900000


  

Best Regards,

Marcos Fattibello

7 REPLIES 7
v-yulgu-msft
Microsoft Employee
Microsoft Employee

Hi @mfattibello,

 

Below is my test.

 

Sample dataset.

1.PNG

 

Create measures.

Ranking =
RANKX (
    ALLSELECTED ( Ranking[Category] ),
    CALCULATE ( SUM ( Ranking[Revenue] ) ),
    ,
    ASC,
    DENSE
)

Running sum =
VAR rankvalue = Ranking[Ranking]
RETURN
    CALCULATE (
        SUM ( Ranking[Revenue] ),
        FILTER ( ALLSELECTED ( Ranking[Category] ), Ranking[Ranking] <= rankvalue )
    )

Result.

4.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MFelix
Super User
Super User

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.

 

cumu.png

 

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

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português




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 😞

 

RKRunning SumRevenue
1316,79316,8
3457,83141
4497,940,1
5212,7731,7
6559,5630
7251,799
8258,586,8

 


Best regards,

Marcos Fattibello

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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

 

Untitled.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

 

Could you share the link from where i can download your workbook.  Please remove information which is not relevnt for the question.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors