TOPN sort order with USERELATIONSHIP

[jthomson]

Hi,

Banging my head against the wall trying to work out how to get this to work. This is the measure in question:

Top2% = 
var claims = CALCULATE(COUNTROWS(dcl),USERELATIONSHIP(DateTable[Date],dcl[Settled Date]))
var top2calc = CALCULATE(ROUNDDOWN(divide(claims,50),0),USERELATIONSHIP(DateTable[Date],dcl[Settled Date]))
var sumofthat = CALCULATE(sum(dcl[Total Incurred]),topn(top2calc,dcl,dcl[Total Incurred],desc))
return divide(sumofthat,top2calc)

Basically I have a date table which does what it says, then I have a table dcl, which has a list of data with three pertinent fields:

- reported date, which has the active relationship between dcl and the date table

- settled date, which has an inactive relationship between dcl and the date table

- total incurred, which is the important value on each line

What I'm wanting to do is to calculate the average cost of the top 2% of claims based on when they were settled - to do this, I'm using the variable claims to work out how many claims were settled in a given time period, then using the variable top2calc to work out how many of these claims I'm interested in looking at. Both of these work. The issue I'm having is with the variable sumofthat - as of right now, it is correctly returning the sum of the top 2% of claims, but based on reporting date, and I'm not sure where exactly in the measure to tell it to use the other relationship instead - most places where i've put in USERELATIONSHIP that I've tried has caused the measure to not return a value at all. Any ideas on how to fix?

[v-chenwuz-msft · Accepted solution]

Hi @jthomson ,

You want to add Userelationship to sumofthat?

If yes, please try add CALCULATETABLE() to the TOPN()'s table, second parameter.

Top2% =
VAR claims =
    CALCULATE(
        COUNTROWS( dcl ),
        USERELATIONSHIP ( DateTable[Date], dcl[Settled Date] )
    )
VAR top2calc =
    CALCULATE(
        ROUNDDOWN( DIVIDE( claims, 50 ), 0 ),
        USERELATIONSHIP ( DateTable[Date], dcl[Settled Date] )
    )
VAR sumofthat =
    SUMX(
        TOPN(
            2,
            CALCULATETABLE( 'dcl', USERELATIONSHIP ( DateTable[Date], dcl[Settled Date] ) ),
            [total incurred], DESC
        ),
        [total incurred]
    )
RETURN
    DIVIDE( sumofthat, top2calc )

If i misunderstood you, please share your pbix file without sensitive data and expect result.

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.