Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
adim
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:

pivotpivot

and here are my 3 tables:

 

tablestables

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

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

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

7 REPLIES 7
TomMartens
Super User
Super User

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

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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!

 

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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

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!

 

Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.