cancel
Showing results for
Did you mean:  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: Matrix i want to achieve: 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

Please let me know in case of any questions.

Thanks

Sanchit

1 ACCEPTED SOLUTION  Community Support

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

4 REPLIES 4  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  Community Support

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

Best Regards,
Eyelyn Qin  Community Support

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: 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.  Helper I

This article by the guys at Dax Patterns should tell you everything you need to know. Announcements #### Welcome to the User Group Public Preview  