cancel
Showing results for 
Search instead for 
Did you mean: 
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
Eyelyn9
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 @Eyelyn9 ,

 

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

Eyelyn9
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

lance_6
Helper I
Helper I

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors