How Can I create a measure which is the reverse of another measure

[jiangchuan23]

I have a measure created by as following:

cumulative good=CALCULATE(sum(table[good]),FILTER(ALLSELECTED(table),table[Custom Score Gains (bins)] <= max(table[Custom Score Gains (bins)])))

Herer is table:

I would like to create a new measure which is the reserve order of measure cumulative good. So it will start value 345768 at frist bin 387. Then the second bin value will be 345118. ............................ and the last bin value would be 2. How I can I create such measure ?

Thanks,

[Vvelarde]

@jiangchuan23

Hi, please try with this: **Adapt the measure to your scenario.

Measure =
VAR Category =
    SELECTEDVALUE ( Table1[Category] )
VAR CategoriesTotal =
    CALCULATE ( DISTINCTCOUNT ( Table1[Category] ); ALL ( Table1 ) )
VAR Categoriesbefore =
    CALCULATE (
        DISTINCTCOUNT ( Table1[Category] );
        FILTER ( ALL ( Table1 ); Table1[Category] < Category )
    )
VAR CategoriesAfter = CategoriesTotal - Categoriesbefore
RETURN
    CALCULATE (
        SUM ( Table1[Value] );
        TOPN ( CategoriesAfter; ALL ( Table1 ); Table1[Category]; ASC )
    )

Regards

Victor

[jiangchuan23]

THanks Victor. I tried your solution but still can't get it to work. Do you know why ?

Measure =
VAR Category =
SELECTEDVALUE ( tracking_data_combined2[FICO_BIN] )
VAR CategoriesTotal =
CALCULATE ( DISTINCTCOUNT ( tracking_data_combined2[FICO_BIN] ), ALLSELECTED(tracking_data_combined2 ) )
VAR Categoriesbefore =
CALCULATE (
DISTINCTCOUNT ( tracking_data_combined2[FICO_BIN] ),
FILTER ( ALLSELECTED( tracking_data_combined2 ), tracking_data_combined2[FICO_BIN]<Category) )
VAR CategoriesAfter = CategoriesTotal - Categoriesbefore
RETURN
CALCULATE (
SUM ( tracking_data_combined2[good]),
TOPN ( CategoriesAfter, ALLSELECTED( tracking_data_combined2),tracking_data_combined2[FICO_BIN], asc)
)

 

[jiangchuan23]

Anyone knew how to create such measure ?

[Aron_Moore]

Maybe cumulative good2=SUM(table[good])-CALCULATE(sum(table[good]),FILTER(ALLSELECTED(table),table[Custom Score Gains (bins)] <= max(table[Custom Score Gains (bins)])))

[jiangchuan23]

Thanks for your reply. Not really working 

[Zubair_Muhammad]

@jiangchuan23

I am not sure. But give it a shot.

First create these RANK MEASURES

RANK Asc =
RANKX (
    ALLSELECTED ( 'Table'[Custom Score Gains (bins)] ),
    [cumulative good],
    ,
    ASC
)

RANK Desc =
RANKX (
    ALLSELECTED ( 'Table'[Custom Score Gains (bins)] ),
    [cumulative good],
    ,
    DESC
)

[Zubair_Muhammad]

@jiangchuan23

Using the above RANK measures... you might get the good column in reverse order using this MEASURE

Reverse good =
VAR myrank = [RANK Asc]
RETURN
    CALCULATE (
        SUM ( 'Table'[good] ),
        FILTER (
            ALLSELECTED ( 'Table'[Custom Score Gains (bins)] ),
            [RANK Desc] = myrank
        )
    )

Then you can cumulate this ReverseGood measure

[jiangchuan23]

@Zubair_Muhammad

Muhammad, Do you know how to do cumulative for the measure ReverseGood ?

[Zubair_Muhammad]

@jiangchuan23

Could you share your file?

I will look into it

[jiangchuan23 · Accepted solution]

I get it working .Thanks guys

@Zubair_Muhammad

@Vvelarde 

Cumulative good(cbr)2 = CALCULATE(sum(tracking_data_combined2[good]),FILTER(ALLSELECTED(tracking_data_combined2),tracking_data_combined2[FICO_BIN] <= MAX(tracking_data_combined2[FICO_BIN])))

measure 3 =
Var RANK_desc =
RANKX (
    ALLSELECTED ( table[FICO_BIN] ),
    [Cumulative good(cbr)1],
    ,
   DESC
)
return
CALCULATE(sum(table[good]),TOPN (RANK_desc, SUMMARIZE(ALLSELECTED( table),table[FICO_BIN],"total good",sum(table[good]))table[FICO_BIN],ASC))

[jiangchuan23]

Thanks for your reply Muhammad. 

Here is the snapshot after I implemented your code. In my data, there is a few bins Dont have any obs in it