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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
angelocecinati
Frequent Visitor

Cumulative Total over a measure

Hello everyone!

 

I have to create a cumulative value (MG%Cumulative) according to a rank measure (Rank).
I had some trouble using the standard calculate-filter solution cause the filter function requires a column from the underlying table, while I have to filter according to a measure.

 

ItemRevenueRankMg%Mg%Cumulative
101100.000140%40%
10380.000230%70%
10250.000320%90%
10510.000410%100%

 

NOTE: The pbix is created with a direct query on a SSAS Tabular cube and I've no acces to the underlying tables.

Thus I should resolve the issue just using measures.
Thanks in advance,
Angelo

2 ACCEPTED SOLUTIONS

@angelocecinati

 

Try this MEASURE in ITEMSTABLE

 

Revenue Cumulative =
VAR CurrentRow = [Rank]
RETURN
    SUMX (
        FILTER ( ALLSELECTED ( ItemsTable[Item] ), [Rank] <= CurrentRow ),
        [Revenue]
    )

Regards
Zubair

Please try my custom visuals

View solution in original post

Thanks to Marco Russo I found a solution that avoid the uses of the VAR function!

 

https://www.sqlbi.com/blog/marco/2014/02/20/the-cumulative-total-dax-pattern/#comment-3722046622

 

Thanks everyone,

 

Angelo

View solution in original post

6 REPLIES 6
v-shex-msft
Community Support
Community Support

Hi @angelocecinati,

 

Can you share more detailed contents about your situation?
I'm not so sure how MG measure calculated, it will be help if you share measure formula and expected result.

 

Regards,

Xiaoxin Sheng

 

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft,

 

Sure! The MG measure is not so important. We can simplify further the example in this way:

 

ItemRevenueRankRevenue Cumulate
101100.0001 100.000   
10380.0002 180.000   
10250.0003 230.000   
10510.0004 240.000   

 

I want to compute the Revenue Cumulate according to the Rank measure.

The Revenue is a measure coming directly from the cube. And I've not visibility about its computation.

The Rank is a measure computed as follow:

Rank = if (HASONEVALUE

                          (ItemsTable[Items])

                          ,RANKX(ALL(ItemsTable[Items])

                                        ,[Revenue],,,Dense)

                 ,BLANK()

                 )

The Revenue Cumulate has to show the highest revenue in the first row, the sum of the first and the second highest in the second row and so on.

 

I hope to be clear,

Thanks a lot,

 

Angelo

@angelocecinati

 

Try this MEASURE in ITEMSTABLE

 

Revenue Cumulative =
VAR CurrentRow = [Rank]
RETURN
    SUMX (
        FILTER ( ALLSELECTED ( ItemsTable[Item] ), [Rank] <= CurrentRow ),
        [Revenue]
    )

Regards
Zubair

Please try my custom visuals

Thanks thats great! Excellent Solution!

Hi @Zubair_Muhammad,

 

Thanks a lot for your hint! I replicated it in the sample file and it works!

By the way if I recreate this measure on the SSAS cube .pbix it shows me an error on the var definition:Cattura.PNGIt is due to the fact that the Rankx and the VAR functions are not accepted in direct query. I can rank directly using the revenue field. What about the VAR function use? Do you have any workaround?

 

Thanks Angelo

 

 

Thanks to Marco Russo I found a solution that avoid the uses of the VAR function!

 

https://www.sqlbi.com/blog/marco/2014/02/20/the-cumulative-total-dax-pattern/#comment-3722046622

 

Thanks everyone,

 

Angelo

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.