Creating DAX peer measure

1
votes

The scenario:

We are an insurance brokerage company. Our fact table is claim metrics current table. This table has unique rows for multiple claim sid-s, so that, countrows(claim current) gives the correct count of the number of unique claims. Now, this table also has clientsid and industrysid. The relation between client and industry here is that, 1 industry can have multiple clients, and 1 client can belong to only 1 industry.

Now, let us consider a fact called claimlagdays, which is present in the table at the granularity of claimsid.

Now, one requirement is that, we need to find out "peer" sum(claimlagdays). This, for a particular client, is basically calculated as:

sum(claimlagdays) for the industry of the client being filtered (minus) sum(claimlagdays) for this particular client. Let's call this measure A.

Similar to above, we need to calculate "peer" claim count , which is claimcount for the industry of the client being filtered (minus) claimcount for this particular client. Let's call this measure B.

In the final calculation, we need to divide A by B, to get the "peer" average lag days.

So basically, the hard part here is this: find the industry of the particular client which is being filtered for, and then, apply this filter to the fact table (claim metrics current) to find out the total claim count/other metric only for this industry. then of course, subtract the client figure from this industry figure to get the "peer" measure. This has to be done for each row, keeping intact any other filters which might be applied in the slicer(date/business unit, etc.)

There are a couple of other filters static which need to be considered, which are present in other tables, such as "Claim Type"(=Indemnity/Medical) and Claim Status(=Closed).

My solution:

For measure B

I tried creating a calculated column, as:

Claim Count_WC_MO_Industry=COUNTROWS(FILTER(FILTER('Claim Metrics Current',RELATED('Claim WC'[WC Claim Type])="Medical" && RELATED('Coverage'[Coverage Code])="WC" && RELATED('Claim Status'[Status Code])="CL"),EARLIER('Claim Metrics Current'[IndustrySID])='Claim Metrics Current'[IndustrySID]))

Then I created the measure

Claim Count - WC MO Peer:=CALCULATE(SUM([Claim Count_WC_MO_Industry])/[Claim - Count])- [Claim - Count WC MO]

{I did a sum because, tabular model doesn't directly allow me to use a calculated column as a measure, without any aggregation. And also, that wouldn't make any sense since tabular model wouldn't understand which row to take}

The second part of the above measure is obviously, the claim count of the particular client, with the above-mentioned filters.

Problem with my solution:

The figures are all wrong.I am not getting a client-wise or year-wise separation of the industry counts or the peer counts. I am only getting a sum of all the industry counts in the measure.

My suspicion is that this is happening because of the sum which is being done. However, I don't really have a choice, do I, as I can't use a calculated column as a measure without some aggregation...

Please let me know if you think the information provided here is not sufficient and if you'd like me to furnish some data (dummy). I would be glad to help.

1
dax
Are all those related values required for the base measure, or is that a specific example? – Kyle Hale

1 Answers

0
votes

So assuming that you are filtering for the specific client via a frontend, it sounds like you just want

ClientLagDays :=
CALCULATE (
    SUM ( 'Claim Metrics Current'[Lag Days] ),
    Static Filters Here
)

Just your base measure of appropriate client lag days, including your static filters.

IndustryLagDays :=
CALCULATE (
    [ClientLagDays],
    ALL ( 'Claim Metrics Current'[Client] ),
    VALUES ( 'Claim Metrics Current'[IndustrySID] )
)

This removes the filter on client but retains the filter on Industry to get the industry-wide total of lag days.

PeerLagDays:=[IndustryLagDays]-[ClientLagDays]

Straightforward enough.

And then repeat for claim counts, and then take [PeerLagDays] / [PeerClaimCount] for your [Average Peer Lag Days].