cancel
Showing results for
Search instead for
Did you mean:
Frequent Visitor

## SUMMARIZECOLUMNS, AVERAGE, FILTER

Hi there,

I'm trying to replicate the output of an Excel pivot table by using DAX. But I'm brand new to DAX....

Here is my pivot table:

pivot

and here are my 3 tables:

tables

The pivor shows the average of claims[amount] for each people[sex], grouped by people[age].

What I got until now in DAX is this:

```EVALUATE
SUMMARIZECOLUMNS(
people[age],
FILTER(people,people[gender]="f"),
"Avg_female", AVERAGE(claims[amount])
)
ORDER BY people[age]```

which generates this table:

This is the first column of my pivot, how can I add the average of male gender?

Finally, I will need to filter the results (by using slicers and timelines), so if you can show me how to filter the final table based on counties[state] and claims[date] that would be great!

Thank you for your time!

Adrian

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

## Re: SUMMARIZECOLUMNS, AVERAGE, FILTER

Hey,

not sure if I fully understand your requirements, but I would start with something similar to this:

```EVALUATE
SUMMARIZECOLUMNS(
people[age],
"Avg_female", CALCULATE(AVERAGE(claims[amount]),FILTER(people,people[gender]="f"))
,"Avg_male", CALCULATE(AVERAGE(claims[amount]),FILTER(people,people[gender]="m"))
)
ORDER BY people[age]```

Maybe this is what you are looking for.

Regards,

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
7 REPLIES 7
Super User

## Re: SUMMARIZECOLUMNS, AVERAGE, FILTER

Can you post sample data? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Honestly, I'm not sure you have to do all of this work in DAX versus just using a Matrix visualization and maybe some simple measures.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Frequent Visitor

## Re: SUMMARIZECOLUMNS, AVERAGE, FILTER

Sorry, here is a couple of rows of data for each table.

I'm trying to do a table based on a custom DAX directly in Excel, by using Excel's data model.

counties:

 id county_code county_name state state_abbrev population 2489 48001 Anderson Texas TX 9876 2490 48003 Andrews Texas TX 6170 2491 48005 Angelina Texas TX 1222

people:

 person_id county_code age weight gender 1545191196 8027 9 104 m 9996085812 48309 9 280 m 1039327894 48197 9 139 m

claims:

 claim_id person_id date amount treatment_days 10000005 0022707752 1/1/2006 438.71 8 10000111 1115974936 1/1/2006 918.17 8 10000139 1481138056 1/1/2006 460.03 8
Highlighted
Super User

## Re: SUMMARIZECOLUMNS, AVERAGE, FILTER

Hey,

not sure if I fully understand your requirements, but I would start with something similar to this:

```EVALUATE
SUMMARIZECOLUMNS(
people[age],
"Avg_female", CALCULATE(AVERAGE(claims[amount]),FILTER(people,people[gender]="f"))
,"Avg_male", CALCULATE(AVERAGE(claims[amount]),FILTER(people,people[gender]="m"))
)
ORDER BY people[age]```

Maybe this is what you are looking for.

Regards,

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Frequent Visitor

## Re: SUMMARIZECOLUMNS, AVERAGE, FILTER

Hi Tom,

You understood perfectly, that's exactly what I need, thank you!

I just need one more thing, to use slicers and timelines for filtering, which means adding two general filters:

• claims[date] between some date
• counties[state] = some state

Looks like I can do it by adding the filter in each gender block, like this:

```EVALUATE
SUMMARIZECOLUMNS (
people[age],
"Avg_female", CALCULATE (
AVERAGE ( claims[amount] ),
FILTER ( people, people[gender] = "f" ),
FILTER ( counties, counties[state] = "Texas" )    ),
"Avg_male", CALCULATE (
AVERAGE ( claims[amount] ),
FILTER ( people, people[gender] = "m" ),
FILTER ( counties, counties[state] = "Texas" )    )
)
ORDER BY people[age]```

but is there any way to filter the whole table at once?

Thanks again!

Super User

## Re: SUMMARIZECOLUMNS, AVERAGE, FILTER

Hey,

maybe this is what you are looking for:

```EVALUATE
CALCULATETABLE(
SUMMARIZECOLUMNS (
people[age],
"Avg_female", CALCULATE (
AVERAGE ( claims[amount] ),
FILTER ( people, people[gender] = "f" )
),
"Avg_male", CALCULATE (
AVERAGE ( claims[amount] ),
FILTER ( people, people[gender] = "m" )
)
)
,FILTER ( counties, counties[state] = "Texas" )
)
ORDER BY people[age]```

Regards,

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Frequent Visitor

## Re: SUMMARIZECOLUMNS, AVERAGE, FILTER

I'm getting a "SummarizeColumns() and AddMissingItems() may not be used in this context." error when trying the last code.

Any other approach? If not, I will stick with filters for each group....

Frequent Visitor

## Re: SUMMARIZECOLUMNS, AVERAGE, FILTER

I managed to get it working by replacing SUMMARIZECOLUMNS with SUMMARIZE. Here is the code:

```EVALUATE
CALCULATETABLE(
SUMMARIZE (
claims,
people[age],
"Avg_female", CALCULATE (
AVERAGE ( claims[amount] ),
FILTER ( people, people[gender] = "f" )
),
"Avg_male", CALCULATE (
AVERAGE ( claims[amount] ),
FILTER ( people, people[gender] = "m" )
)
)
,FILTER ( counties, counties[state] = "Texas" )
)
ORDER BY people[age]```

Thanks for your help, you put me on the right track!