DAX for calculate cross tables

[DPSir]

Hello,

I have problems about Power BI DAX as below. Could you guys please help me?

I have 2 tables:

Sheet 1

Month/Yesr	Type	Qty
10/23	A	10
10/23	B	20
11/23	A	20
11/23	C	10
12/23	A	30
12/23	B	30
01/24	A	40
01/24	B	5

Sheet2:

Month/Yesr	Type	Qty
10/23	A	2
11/23	A	1
11/23	C	1
01/24	A	1
01/24	B	4

I connected model like this:

What I want to calculate is (sheet 2 qty / sheet 1 qty) *100 but want to show in line chart as y-axis , x-axis is Month/year.

As example result that I want for 10/23 of A is (2/10)*100 = 20 and not show B
But in my Power BI shown (2/30)*100 = 6.67 [Power BI sum A and B together] [my DAX is Measure = SUM(Sheet2[Qty])/SUM(Sheet1[Qty])*100] as chart below

How can I fix this?

[Anonymous · Accepted solution]

Hi @DPSir ,

I think I understand what you're trying to do, you just want to calculate about A and you don't want to bring in the value of B,

so in this case, we can use our DAX statement to add two filters to help me accomplish this.

SumQtySheet1 = CALCULATE(SUM(Sheet1[Qty]),FILTER('Sheet1',Sheet1[Month/Yesr]IN VALUES(Sheet2[Month/Yesr])&&'Sheet1'[Type] IN VALUES(Sheet2[Type] )))

SumQtySheet2 = CALCULATE(SUM(Sheet2[Qty]),FILTER(Sheet2,'Sheet2'[Month/Yesr]IN VALUES(Sheet1[Month/Yesr])&&Sheet2[Type] IN VALUES(Sheet1[Type]))) 

Measure = DIVIDE([SumQtySheet2],[SumQtySheet1],0) *100

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[DPSir]

It's work. Thank you very much.🍻

[DPSir]

Here is files: https://drive.google.com/drive/folders/1a4nE6XyIOfBKJ39-Boc8EwJgtTq1T4wR?usp=sharing
This is expectation result: