Running Total of a Measure

[Anonymous]

Hi,

I have the following data:

I need to calculate the running total of the measure '% of Value Claimed'. So something like this:

49.69%

74.69%

85.84%

89.17% etc

The DAX for the '% Of Value Claimed' measure is:

% Of Value Claimed = DIVIDE(SUM(ClaimLine[ClaimedAmt]); CALCULATE(SUM(ClaimLine[ClaimedAmt]); ALLSELECTED(ClaimLine)))

Any help appraciated.. Thanks

[PaulDBrown]

@Anonymous 

You can achieve this by using a combination of RANKX and TOPN.

See this example: this is a simple model calculating the cumulative values for "Actuals" by "Channel"

 And here are the measures:

Sum of Actuals = SUM('Data Table'[Actuals])

RankX Actuals = 
VAR calc = RANKX(ALLSELECTED('Data Table'[Channel]);[Sum of Actuals];; DESC;Dense)
RETURN
IF(ISINSCOPE('Data Table'[Channel]); calc; BLANK())

And finally the cumulative values:

Cumulative Actuals = SUMX(TOPN([RankX Actuals]; 
                        ALLSELECTED( 'Data Table'[Channel]);[Sum of Actuals]; DESC); 
                            [Sum of Actuals])

[Anonymous]

Hi @PaulDBrown,

Thanks for your input. Didn't work tho.:

Measures are:

% Of Value Claimed = DIVIDE(SUM(ClaimLine[ClaimedAmt]);CALCULATE(SUM(ClaimLine[ClaimedAmt]);ALLSELECTED(ClaimLine)))

RankX % Value CLaimed =

VAR Calc = RANKX(ALLSELECTED(ClaimLine[ChargeCode]);[% Of Value Claimed];; DESC; Dense)

RETURN

IF(ISINSCOPE(ClaimLine[ChargeCode]); Calc; BLANK())

Cumulative % Value Claimed = SUMX(

TOPN(

   [RankX % Value CLaimed];

   ALLSELECTED(ClaimLine[ChargeCode]);[% Of Value Claimed]; DESC

);

[% Of Value Claimed]

Thanks

)

[PaulDBrown]

@Anonymous 

OK, well try using the sum of values instead of the percentages to get the cumulative, as in:
1) calculate total over all selected :

Total Actuals (selected) = CALCULATE([Sum of Actuals]; ALLSELECTED('Data Table'[Channel]))

2) Rank of values:

RankX Actuals = 
VAR calc = RANKX(ALLSELECTED('Data Table'[Channel]);[Sum of Actuals];; DESC;Dense)
RETURN
IF(ISINSCOPE('Data Table'[Channel]); calc; BLANK())

2) Cumulative Values:

Cumulative Actuals = SUMX(TOPN([RankX Actuals]; 
                        ALLSELECTED( 'Data Table'[Channel]);[Sum of Actuals]; DESC); 
                            [Sum of Actuals])

3) now divide the cumulative over the total:

Cumulative % Actuals = DIVIDE([Cumulative Actuals]; [Total Actuals (selected)])

And you get this:

[Gordonlilj]

Hi,

You could try creating a running total quick measure and see if you get the result you need

[Anonymous]

Hi,

The use of the quick measure didn't work (See last column):

The DAX for the Quick measure is:

% Of Value Claimed running total in ChargeCode =

CALCULATE(

    [% Of Value Claimed];

    FILTER(

        ALLSELECTED('ClaimLine'[ChargeCode]);

        ISONORAFTER('ClaimLine'[ChargeCode]; MAX('ClaimLine'[ChargeCode]); DESC)

    )

)