Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
password-12345
Please let me know in case of any questions.
Thanks
Sanchit
Solved! Go to Solution.
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:
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.
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
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:
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.
This article by the guys at Dax Patterns should tell you everything you need to know.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |