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
sanchit_1610
Helper I
Helper I

Calculate the cummulative sum in Matrix with sorting applied on the Measure

Hi All,

 

I have a matrix wherein i am trying to calculate the running total artition by on one dimension, the running total (cummulative sum) should be calculated based on the descending order of the measure. Please find below scenario

Data source:

sanchit_1610_0-1623265420456.png

 

Matrix i want to achieve:

sanchit_1610_1-1623265499949.png

 

The column C has value calculated by running total of machines sold with the first element considered being the largest i.e. in descending order of machines sold.

 

The D column calculates if value in C column is greater than half of the total. In first case half of total i.e. 70/2=35, so if greater than 35 than "Y" else "N"

 

I have uploaded the sample excel and the pbix 

https://easyupload.io/cbq9my

password-12345

 

Please let me know in case of any questions.

 

Thanks

Sanchit

1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

Hi @sanchit_1610 ,

 

1. Create a Rank measure to calculate Cummulative sum

Rank = 
var _rank= RANKX (
    FILTER ( ALL ( 'Sheet1' ),'Sheet1'[Model]= MAX('Sheet1'[Model] )   ),
    CALCULATE ( MAX ( ( 'Sheet1'[Machines Sold])) + INT(RIGHT(MAX('Sheet1'[Feature]),1))   ),
    ,
    DESC,Dense
) 
return IF(HASONEVALUE(Sheet1[Feature]),_rank,BLANK())

 

2. Cummulative sum:

Cummulative sum = 
VAR _sum =
    CALCULATE (
        SUM ( 'Sheet1'[Machines Sold] ),
        FILTER (
            ALL ( Sheet1 ),
            'Sheet1'[Model] = MAX ( 'Sheet1'[Model] )
                && [Rank] <= MAXX ( 'Sheet1', [Rank] )
        )
    )
RETURN
    IF ( HASONEVALUE ( Sheet1[Feature] ), _sum, BLANK () )

 

3. Crossed 50% Sale :

Crossed 50% Sale = 
VAR _half =
    CALCULATE (
        SUM ( Sheet1[Machines Sold] ),
        ALLEXCEPT ( 'Sheet1', Sheet1[Model] )
    ) / 2
VAR _flag =
    IF ( [Cummulative sum] <= _half, "N", "Y" )
RETURN
    IF ( HASONEVALUE ( Sheet1[Feature] ), _flag, BLANK () )

  

4. Number of Features taken for 50% sale:

Number of Features taken for 50% sale = 
IF (
    HASONEVALUE ( Sheet1[Model] ),
    IF (
        HASONEVALUE ( 'Sheet1'[Feature] ),
        BLANK (),
        MINX ( FILTER ( 'Sheet1', [Crossed 50% Sale] = "Y" ), [Rank] )
    ),
    BLANK ()
)

 The final output is shown below:

cummulative sum and rank.PNG

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
sanchit_1610
Helper I
Helper I

Hi @v-eqin-msft ,

 

Thanks for helping here. 

 

Can you help me if we have similar issue to resolve but the Machine Sold is a calaculated measure?

 

Thanks

Sanchit

Hi @sanchit_1610 ,

 

I'd suggest you create another thread since this post has been closed. So that more engineers could help you.

 

Best Regards,
Eyelyn Qin

v-eqin-msft
Community Support
Community Support

Hi @sanchit_1610 ,

 

1. Create a Rank measure to calculate Cummulative sum

Rank = 
var _rank= RANKX (
    FILTER ( ALL ( 'Sheet1' ),'Sheet1'[Model]= MAX('Sheet1'[Model] )   ),
    CALCULATE ( MAX ( ( 'Sheet1'[Machines Sold])) + INT(RIGHT(MAX('Sheet1'[Feature]),1))   ),
    ,
    DESC,Dense
) 
return IF(HASONEVALUE(Sheet1[Feature]),_rank,BLANK())

 

2. Cummulative sum:

Cummulative sum = 
VAR _sum =
    CALCULATE (
        SUM ( 'Sheet1'[Machines Sold] ),
        FILTER (
            ALL ( Sheet1 ),
            'Sheet1'[Model] = MAX ( 'Sheet1'[Model] )
                && [Rank] <= MAXX ( 'Sheet1', [Rank] )
        )
    )
RETURN
    IF ( HASONEVALUE ( Sheet1[Feature] ), _sum, BLANK () )

 

3. Crossed 50% Sale :

Crossed 50% Sale = 
VAR _half =
    CALCULATE (
        SUM ( Sheet1[Machines Sold] ),
        ALLEXCEPT ( 'Sheet1', Sheet1[Model] )
    ) / 2
VAR _flag =
    IF ( [Cummulative sum] <= _half, "N", "Y" )
RETURN
    IF ( HASONEVALUE ( Sheet1[Feature] ), _flag, BLANK () )

  

4. Number of Features taken for 50% sale:

Number of Features taken for 50% sale = 
IF (
    HASONEVALUE ( Sheet1[Model] ),
    IF (
        HASONEVALUE ( 'Sheet1'[Feature] ),
        BLANK (),
        MINX ( FILTER ( 'Sheet1', [Crossed 50% Sale] = "Y" ), [Rank] )
    ),
    BLANK ()
)

 The final output is shown below:

cummulative sum and rank.PNG

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

lance_6
Helper II
Helper II

This article by the guys at Dax Patterns should tell you everything you need to know. 

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.