Sales less average sales per category

[JJ2022PBI]

Hi,

Probably a simple one but I can't figure out how to do this.

I have the below data and I want to create a table which shows the variance in total sales for a product to that of the average of that category. In the below I have calcualted the total sales by products marked as a rating of 5 (3,350), then divided this by the amount of products in this category (3) to get the average of 1,117.

In my visualisation I then show the total sales by product next to average for that category and then a variance.

Thanks!

[Anonymous · Accepted solution]

Hi  @JJ2022PBI ,

Here are the steps you can follow：

1. Create measure.

Flag =
 IF(
     MAX('Fact Table'[Product])<>"C",1,0)

Total Sales =
SUMX(FILTER(ALL('Fact Table'),
'Fact Table'[Product]=MAX('Fact Table'[Product])
),[Sales])

Average for category =
var _sum =
SUMX(ALLSELECTED('Fact Table'),
[Sales])
var _count=
CALCULATE(DISTINCTCOUNT('Fact Table'[Product]),
ALLSELECTED('Fact Table'))
return
DIVIDE(_sum,_count)

Variance =
[Total Sales] - [Average for category]

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly