Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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!
Solved! 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.
You should use CALCULATE(table, AVERAGE(Vendas), ALL (column name that you want to exclude for granularity))
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.
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |