Get counts based on Total

[scabral]

Hi,
I created a PowerPivot workbook with 1 fact table and about 10-12 dimension tables with insurance claim information. There is 1 row in the fact table for every claim transaction for each customer.


the fact table contains a claim number field and a Loss_Paid field. for example:


CustNum Loss_Paid
123 100

123 0

456 0

789 100

789 100


What I would like to do is give the user the ability to get the distinct number of Customers who had total Losses Paid > 0 and vice versa (Total Losses Paid < 0).

I would like to possibly create a calculation and have them be able to use it as a slicer, for example Close_With_Payment and Close_Without_Payment. Whenever they click on one or the other, the numbers would change in the cube.

So Claim_Num 123 and 789 would be included in the Total_Loss_Paid >0 calculation and Claim_Num 456 would be included in the Total_Losses_Paid < 0 Calculation.


Any ideas on how to do this?


thanks
Scott

 

[Jacob Barnett]

Scott,

In simple terms this is straightforward:

Approach 1
Customer Count
This would be a measure that looks something like this - [Customer Count] = DISTINCTCOUNT('facttable'[CustNum])

Loss Paid Status
Create a calculated column on the PowerPivot table like this - [Customer Status] = if([Loss_Paid]>0,"Paid","Not Paid")

You can then use this Customer Status as a Dmension to drag onto the rows or columns of the pivot

Approach 2
Customer Count by Type with no calculated column
This would be two measures that each assess whether or not the customer has been paid which references a base customer count measure like the one above:

[Paid Customer Count] = CALCULATE([Customer Count], FILTER('facttable','facttable'[Loss Status]>0))
[Non Paid Customer Count] = CALCULATE([Customer Count], 'facttable'[Loss Status]=0))

Note the different approaches for the two measures - if you are using < > or a measure in the filter expression then you must use the FILTER() function, otherwise you can use a plain calculate.

Approach 1 might be easier but Approach 2 takes a step towards some of the more exotic DAX functions which are absolutely crucial. Approach 1 also might have performance issues with that calculated column.