CODEWITHSUNDEEP
powerbidax
user882670
user882670

Reputation:

Calculate average within category

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:

enter image description here

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.

enter image description here

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!

Upvotes: 1

Views: 10598

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40264

See if either of these work for you:

Margem Média País = AVERAGEX(ALL(Clientes[Cliente]); [Margem Líquida *])

Margem Média País = CALCULATE(
                        AVERAGEX(Clientes; [Margem Líquida *]);
                        ALL(Clientes[Cliente]))

If not, then please post a minimal complete verifiable example of your data.

Edit: Something like this might help with the issue you mentioned in your comment.

Margem Média País = CALCULATE(
                        AVERAGEX(Clientes; [Margem Líquida *]);
                        ALL(Clientes),
                        Geo[País] IN VALUES(Geo[País]))

Upvotes: 1

Related Questions