Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
107 | |
69 | |
68 | |
43 |
User | Count |
---|---|
148 | |
104 | |
102 | |
89 | |
66 |