Dear All,
My Power Pivot knowledge is basic at best, but I am attempting to create a dashboard using Power Pivot. The amount and complexity of our data was just too much for Power Query alone to handle.
I am attaching a file with a simplified sample of the data.
I have two fact tables - Policies and Claims. Some of the columns are in common - for example State. I created a "lookup" table for State and when I insert a pivot table based on this common column, all values work out fine - see Example # 1 in my file.
Another common field between the two tables is Policy number. In Policies table it is always a unique value, in Claims table it can repeat.
I created a "PolicyID" table as a "lookup" table to join Policies fact table to Claims fact table.
Here is where my troubles start - if I try to create a pivot table with row values from Policies table (for example Construction), then amounts from Claims table are incorrect - Example # 2 in the file. If I create a pivot with row values from Claims table (for example Claim Status), then all the values from Policies are incorrect - Example # 3 in the file.
I am pretty sure that special measures have to be written for this, but I have no idea how. I remember that the filters flow down from look up tables to fact tables, not uphill. I tried variations of CALCULATE with FILTER, VALUES, etc. but clearly I don't know what I'm doing. I don't even know if it's a simple solution, or a complicated one.
Can anyone help, please?
Thank you,
Blanka
Hi Blanka,
If you want to look at claims by 'Construction' then you need to assign that field to the Policy number in the PolicyID table. Then you'd use the 'Construction' field from the dimension table; PolicyID.
You can easily change the Query for PolicyID to not remove the 'Construction' column.
Mynda
Hi Mynda,
Thank you for your reply.
I have many more fields in my data that I need to use for analysis. Construction was just an example. And this would not solve my problem if I wanted to look at data by fields from the Claims report, would it?
For some reason I thought that the answer would be in the measures.
I'll have to keep playing with the data to figure out how best solve it.
Thank you,
Blanka
Measures can only work if the data structure is correct and the relationship exists. If you take the Construction scenario and understand how the model needs to be set up for that to work, hopefully you can apply it to your other fields.
See example attached, I've fixed the structure and added two PivotTables to the Policy Sheet. Notice where the fields come from in the Rows area; they are the dimension table that relates the two fact tables (tblClaimes and Policies) together.
I think you need to review the Power Pivot course session on modelling 6.07 and 6.08.
Mynda
Thank you so much, Mynda.
I'll work on my data following your example.
Blanka
Mynda,
This didn't work for me the way as intended, because some policies can have multiple claims and some of those claims can be open while others can be closed, each claim can have a different Claim Type. In the sample data that I submitted there were three policies with multiple claims and so happened that the claims for each of these policies had the same status.
The PQ that you created for my dimension table removes duplicate policy numbers after merging with Claims Data. This will not account for the fact that one policy can have multiple Claim Status values. Or that one policy can have multiple Claim Type values. For example, Policy A00788 has one Claim Type = Fire, and one Claim Type = Property Damage.
If I don't remove the duplicate policy numbers from my dimension table then I am getting an error message that duplicate values are not allowed for columns on the one side of a many-to-one relationship.
So, I still don't know how to relate Policy data to Claims data. Is it even possible for my data model?
Thank you,
Blanka
Hi Blanka,
I don't see why you can't use the Claim Type field from the Claims table. Please provide an example of the error and what the correct result should be because checking policy A00788 I can see the correct amounts for the claim types, construction, premium, claim incurred and claim count when I add the 'ClaimType' column to the rows area from the 'tblClaims'.
Mynda
