cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Post Prodigy
Post Prodigy

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

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
Advocate II
Advocate II

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

Hi,
CALCULATE requires an expression as the 1st argument, not a table.

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
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session Drive Data Culture with Power BI: Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors