cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
adim Frequent Visitor
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.... Smiley Happy

Here is my pivot table:

pivot.pngpivot

and here are my 3 tables:

 

tables.PNGtables

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:

DAX.PNG

 

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
Super User
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
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!


adim Frequent Visitor
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:

 

idcounty_codecounty_namestatestate_abbrevpopulation
248948001AndersonTexasTX9876
249048003AndrewsTexasTX6170
249148005AngelinaTexasTX1222

 

people:

 

person_idcounty_codeageweightgender
154519119680279104m
9996085812483099280m
1039327894481979139m

 

claims:

 

claim_idperson_iddateamounttreatment_days
1000000500227077521/1/2006438.718
1000011111159749361/1/2006918.178
1000013914811380561/1/2006460.038
Super User
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!
Highlighted
adim Frequent Visitor
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
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!
adim Frequent Visitor
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....

adim Frequent Visitor
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!