Get counts based on Total

scabral

New Member
Joined
Dec 21, 2012
Messages
1
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,226,693
Messages
6,192,460
Members
453,725
Latest member
cvsdatreas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top