CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
mayool
mayool

Reputation: 148

Understanding Averagex - sales per unique customer

I want to calculate the average sales per unique customer:

For this I used:

2avrg_sales_per_customer = AVERAGEX(VALUES(fact_table[customerID]);SUM(fact_table[sales]))

Below you can see how I slice it by year. Screenshot

What I don't understand is the meaning of the total ("Gesamt"). How can it be 956 if, looking at the different years, its ~400-500? If I draw the trendline I see that the average throughout the years is 443,53.

I figuered out, i can get the 443,53 value by using:

5_average_per_year_per_customer =
AVERAGEX(VALUES(dates[date].[year]);[2avrg_sales_per_customer])

The Values for the individual sales stay the same but the total changes.

Can someone explain the difference to me?

Upvotes: 1

Views: 85

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40214

The reason is that the grand total line does not have any filters on year, so it's taking the average of customers summing over all years simultaneously. In the second, it's taking the average of customers per year because that's what you've explicitly specified.

In the first case, you are only splitting by year in the visual whereas in the second case, you are splitting by year in the measure too. The grand total row doesn't know what you decided to split by in the visual so you have to tell it that you want to group by year before taking the average.

Upvotes: 2

Related Questions