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:
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.
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!
Go to 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
You should use CALCULATE(table, AVERAGE(Vendas), ALL (column name that you want to exclude for granularity))
Join us in the third Triple A event!
It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.
Make sure you didn't miss any of the things that happened in the community in January!