cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## DAX for calculate cross tables

Hello,

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 MeasureSUM(Sheet2[Qty])/SUM(Sheet1[Qty])*100] as chart below

How can I fix this?

1 ACCEPTED SOLUTION
Community Support

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.

3 REPLIES 3
Community Support

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.

New Member

It's work. Thank you very much.🍻

New Member

Announcements