topic How do I create a measure to calculate column totals based on filters from other columns in DAX Commands and Tips

How do I create a measure to calculate column totals based on filters from other columns

HCC — Fri, 30 Dec 2022 21:45:02 GMT

Hi all,

I am still new to Power BI but slowly learning! I am used to MS Excel functions but creating MEASURES are completely new to me.

As described in the subject, my goal is to create a measure that totals the monthly employment (shown below) by filtering three other columns as shown below:

I have barely used measures before and I am a little stuck! Any help is greatly appreciated!!!

Re: How do I create a measure to calculate column totals based on filters from other columns

andrewpirie — Sat, 31 Dec 2022 00:57:54 GMT

Hi HHC,

I suggest using CALCULATE filter arguments to achieve this goal, it's a bit long but the sqlbi article here is a good read.

Here's a basic measure using static filters. Apologies for the lack of formatting.

[Sum of month1_emplvl ignoring a specific industry_code and area_fips] =
CALCULATE (
SUM('Sample Data'[month1_emplvl]),
'Sample Data'[industry_code] <> 111100,
'Sample Data'[area_fips] <> "C2683"
)

If you needed to get something like the sum of month1_emplvl with the same area_fips and Year & Quarter columns (regardless of industry_code), here's how you could do a count over all rows with the same values except a couple you want to filter out, and ignoring any differences in one of the columns.

[Sum of month1_emplvl except for 2022-Q1 and C2683] =
CALCULATE (
SUM('Sample Data'[month1_emplvl])
, REMOVEFILTERS('Sample Data'[industry_code])
, 'Sample Data'[Year & Quarter] <> "2022-Q1"
, 'Sample Data'[area_fips] <> "C2683"
)

If I've missed your intention, it could be helpful to have an example of how you'd filter and total using your above dataset in pseudocode or using Excel.

Re: How do I create a measure to calculate column totals based on filters from other columns

tamerj1 — Sat, 31 Dec 2022 10:40:26 GMT

Hi HCC

how does your visual look like?

Re: How do I create a measure to calculate column totals based on filters from other columns

Anonymous — Mon, 02 Jan 2023 05:54:28 GMT

Hi HCC

You can use sumx()

e.g sumx(filter(table,your expression),[month1_emplvl])

if the sample above cannot meet your requirement, can you provide more detail condition?

Best Regards!

Yolo Zhu

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