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
webportal
Impactful Individual
Impactful Individual

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
wildmight2017
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.

 

 

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.