topic Re: calculate sum with multiple criteria in DAX Commands and Tips

calculate sum with multiple criteria

alsm — Sat, 24 Jun 2023 10:07:52 GMT

Hello

I have a table that somewhat looks like

Industry	Sub-Industry	Volume
Energy Equipment & Services	Drilling	100
Energy Equipment & Services	Oil & Gas Equipment & Services	150
Oil, Gas & Consumable Fuels	Drilling	50
Oil, Gas & Consumable Fuels	Integrated Oil & Gas	75
Oil, Gas & Consumable Fuels	Oil & Gas Exploration & Production	200
Metals & Mining	Drilling	225
Metals & Mining	Diversified Metals & Mining	90
Metals & Mining	Copper	75

I want to calculate sum with following filter criteria

Industry IN {Energy Equipment & Services, Oil, Gas & Consumable Fuels} and Sub-Industry = Drilling

Industry IN {Metals & Mining} and Sub-Industry = 'Diversified Metals & Mining'

How to write this combination of AND and OR conditions in Calculate(Sum(Volume),..???..)

Re: calculate sum with multiple criteria

Nathaniel_C — Sun, 25 Jun 2023 00:14:25 GMT

Hi alsm ,

There are many ways to write this, however this works:

Sum = CALCULATE ( SUM ( IndustryResearch[Volume] ), FILTER ( IndustryResearch, IndustryResearch[Industry] IN { "Energy Equipment & Services", "Oil, Gas & Consumable Fuels" } && IndustryResearch[Sub-Industry] = "Drilling" ) ) + CALCULATE ( SUM ( IndustryResearch[Volume] ), FILTER ( IndustryResearch, IndustryResearch[Industry] = "Metals & Mining" && IndustryResearch[Sub-Industry] = "Diversified Metals & Mining" ) )

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Re: calculate sum with multiple criteria

alsm — Sun, 25 Jun 2023 21:43:58 GMT

Nathaniel_C Thank you for your reply, it works. I have a followup question (don't know if I should ask another question or simply here). Can I create a filter variable which has common filter criteria and then use this common filter + additional filter lines in each calculate rows?

Re: calculate sum with multiple criteria

Nathaniel_C — Mon, 26 Jun 2023 01:01:12 GMT

Hi alsm ,

Would you give me an example of what you mean?

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel