Table of ratio with filter from two databases

[hidrag]

Hi everyone,

I have a small issue with Power BI

I have two database

Claims

Quarter	Lob	Value
Q1	A	2500
Q2	A	2000
Q3	A	1500
Q1	B	2400
Q2	B	6000
Q3	B	5000

and Premiums

Quarter	Lob	Value
Q1	A	1000
Q2	A	4000
Q3	A	7000
Q1	B	3500
Q2	B	2700
Q3	B	8000

I want to calculate the claim/premium ratio and put it in a table but i also want filter to see the value by quarter, by lob (read category) and quarter x lob.

What Powerbi does it that it calculates the (sum of all claims)/ (sum of premium for each quarter)

Quarter	Ratio
Q1	4.31
Q2	2.9
Q3	1.29
Total	0.74

How can i do to make the filter use the variable from the two database since they are identical.

[smpa01 · Accepted solution]

@hidrag  what is your desired output

[hidrag]

Thank you for answering me.

Here is what i want to achieve

Quarter	Ratio
Ratio Q1	109%
Ratio Q2	119%
Ratio Q3	43%
Total	74%

Lob	Ratio
Ratio A	50%
Ratio B	94%
Total	74%

	LOB A	LOB  B	TOTAL
Q1	250%	69%	109%
Q2	50%	222%	119%
Q3	21%	63%	43%
TOTAL	50%	94%	74%

I also (if possible) would like the solution to be dynamic in the sense that if there was an additionnal variable to add for my filters i would like to be able to do it simply

[smpa01 · Accepted solution]

@hidrag  pbix is atatched

[smpa01 · Accepted solution]

@hidrag  what is your desired output

[hidrag]

Thanks