Need Help with Data Modeling and Relationships

[efogelberg]

I am creating a data set and PowerBI from scratch to calculate customer claim rates by types of claims. I have three tables below.

I've been able to use certain relationships for slicers to filter by year, but I'd like my data to also filter by type of claim. Essentially, the visual displays Table C's data, a clustered column chart with Companies A, B, and C calculations (claim rates) by Quarter (Q1, Q2, Q3, Q4). I would like to, however, use a slicer that filters out these calculations by Table A's header, Type of Claim. Is there a way to build this relationship between Table A and Table C without adding a million rows/columns of data to one of these tables or creating a new table? I'm a bit lost on where to start modeling my data to make these tables talk to each other the right way.

NOTE:

Claim Rate calculation - (# Claims * 100,000) / # Customers

Currently, Table A has all the data I need but when the sum of these rates doesn't represent the accurately Quarterly rate. The data is summing each individual company's rates rather than adding the # rates together and dividing by the OVERALL number of customers). In other words, the rows represent company-specific rates, and don't provide accurate data for the overall rate by quarter.

[amitchandak]

@efogelberg , TableB need double unpivot(Company and claims) and tableC need unpivot(claims) 

After that, you can have a common dimension date, company

Power Query Magic: Double Unpivot and Reshape Data for Analysis in Power BI| Unpivot Column Set: https://www.youtube.com/watch?v=Diazpx1Nfec&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L&index=1

Unpivot Data(Power Query): https://youtu.be/2HjkBtxSM0g

[efogelberg]

Thank you, I will give this a shot. As for the different in calculation, will this resolve that as well? Currently, it is calculating the Quarterly Rate as a sum of all individual rates (rate is # claims x 100,000/# customers) for that quarter rather than summing the number of claims that quarter and dividing by total number of customers for that quarter. Essentially it's summing the individual calculations rather than doing a new calculation.