topic Re: DAX for calculate cross tables in DAX Commands and Tips

DAX for calculate cross tables

DPSir — Wed, 10 Jul 2024 12:15:12 GMT

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?

Re: DAX for calculate cross tables

DPSir — Wed, 10 Jul 2024 12:37:25 GMT

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

Re: DAX for calculate cross tables

Anonymous — Fri, 12 Jul 2024 02:23:12 GMT

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.

Re: DAX for calculate cross tables

DPSir — Sat, 13 Jul 2024 09:07:53 GMT

It's work. Thank you very much.🍻