cancel
Showing results 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

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.

 RK Revenue RunningSum 1 900000 900000 2 800000 1700000 3 700000 2400000 4 600000 3000000 5 500000 3500000 6 400000 3900000

Best Regards,

Marcos Fattibello

7 REPLIES 7
Microsoft Employee

Hi @mfattibello,

Below is my test.

Sample dataset.

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.

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.
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.

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

Proud to be a Super User!

Check out my blog: Power BI em Português

Regular Visitor

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

Microsoft Employee

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.
Regular Visitor

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

Super User

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com