cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
webportal Member
Member

Calculate average within category

Hi,

 

How to calculate the average of a measure within a category?

 

For example, in my data model, I have a table "Geo" that contains the field "País" (Country).

In table "Clientes" I have the field "Cliente" (Customer).

 

This is how the relationship looks like:

 

Capturar.JPG

 

The facts table is "Vendas".

 

I need the average of measure "Margem Líquida *" per country, so I'm using the following measure:

 

Margem Média País = AVERAGEX(Clientes;[Margem Líquida *])

This is ok on a Country level, but as you can see in the following table I can't compare the value of the measure with the average per country on a Customer ("Cliente") level. 

 

Capturar2.JPG

 

I'd like to have the value of the country level repeated for each customer within the country.

 

How can I do that? I've tried all sorts of CALCULATE with EARLIER but no luck...

 

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
wildmight2017 Regular Visitor
Regular Visitor

Re: Calculate average within category

You're correct, I didn't type it right-  here's what works for me in this situation:

 

Avg Jobs Per Day by Location = CALCULATE (FactFinanceTable[Avg Jobs Per Day],SUMMARIZE ('GeoHierarchyDimTable', GeoHierarchyDimTable[Location]), ALL(EmployeeDimTable[Name] )) 

 

This gives me Average by Location properly.

 

 

View solution in original post

3 REPLIES 3
wildmight2017 Regular Visitor
Regular Visitor

Re: Calculate average within category

You should use CALCULATE(table, AVERAGE(Vendas), ALL (column name that you want to exclude for granularity)) 

webportal Member
Member

Re: Calculate average within category

Hi,
CALCULATE requires an expression as the 1st argument, not a table.
Highlighted
wildmight2017 Regular Visitor
Regular Visitor

Re: Calculate average within category

You're correct, I didn't type it right-  here's what works for me in this situation:

 

Avg Jobs Per Day by Location = CALCULATE (FactFinanceTable[Avg Jobs Per Day],SUMMARIZE ('GeoHierarchyDimTable', GeoHierarchyDimTable[Location]), ALL(EmployeeDimTable[Name] )) 

 

This gives me Average by Location properly.

 

 

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors