Calculate the difference in Average sales between different product types

[Dimitris_Kats]

Hello dear members,

First of all i would like to mention how valuable is your help!! 

I have a problem that i have managed to solve using calculated tables and calculated columns and i was wondering if there is a easier way through measures.

I have a reference table with average, max and min sales per product type in order to categorize them properly.
I need to calculate the difference in average, max and min of the reference table and the actual sales.

This is my reference table
Product Type Average Monthly Sales Max Sales Min Sales
1                    300000                           400000        200000
2                    200000                            250000       100000
3                    100000                           150000         50000
4                    50000                             75000           25000

My actual sales table is:
Date Product Type Store Num Sales
1/1/2022        1         1        200000
1/1/2022        1          2       100000
1/1/2022        1          3         75000
1/1/2022        2         1          150000
1/1/2022        2         2          50000
1/1/2022        2         3          25000
1/1/2022        3         1          100000
1/1/2022        3         2           25000
1/1/2022        3         3          10000
1/1/2022        4        1           50000
1/1/2022       4         2           15000
1/1/2022       4         3            5000
1/2/2022       1        1            250000
1/2/2022       1         2           50000
1/2/2022      1          3           75000
1/2/2022       2         1            125000
1/2/2022       2         2            75000
1/2/2022        2        3            25000
1/2/2022        3         1           80000
1/2/2022        3        2            30000
1/2/2022        3        3            20000
1/2/2022       4         1           40000
1/2/2022       4          2            5000
1/2/2022        4          3           10000

I would like to see what is the average sale of product Type 1 and how big is the difference with the reference average.

Any idea how to achive it using measures??

Thank you in advance

[v-xiaotang · Accepted solution]

Hi @Dimitris_Kats 

You can try this,

(1) //"I would like to see what is the average sale of product Type 1 and how big is the difference with the reference average"

test1 actual = //"I would like to see what is the average sale of product Type 1 and how big is the difference with the reference average"
var _totalPerType= CALCULATE(SUM('actual sales'[Sales]),ALLEXCEPT('actual sales','actual sales'[Product Type]))
var _countMonth= CALCULATE(DISTINCTCOUNT('actual sales'[Date]),FILTER(ALL('actual sales'),'actual sales'[Product Type]=MIN('actual sales'[Product Type])))
return DIVIDE( _totalPerType,_countMonth)

test1 refer = CALCULATE(MAX(reference[Average Monthly Sales]),reference[Product Type]=MIN('actual sales'[Product Type]))

test1 diff = [test1 actual]-CALCULATE(MAX(reference[Average Monthly Sales]),reference[Product Type]=MIN('actual sales'[Product Type]))

 result

(2) //i need to compare the average, min and max of actual sales with the default (reference) table that i was given

create the measures below, for more please see the sample file attached below

result

(3) // i want to divide the sales of product 1 in store 1 with the average reference sales (200000/300000)

test3 = // i want to divide the sales of product 1 in store 1  with the average reference sales (200000/300000)
DIVIDE( MIN('actual sales'[Sales]),[test1 refer])

result

Best Regards,

Community Support Team _Tang

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

[Dimitris_Kats]

@amitchandak thank you very much, i am going to check this out immediately.
Also i need to check every single product's sales and compere them with the average reference sales.
For example i want to divide the sales of product 1 in store 1  with the average reference sales (200000/300000)
Any idea how i am going to do this between those 2 tables??

[Dimitris_Kats]

@amitchandak  thank you very much for your reply.
Can you please demonstrate how to use these measures??
Actual you are right, i need to compare the average, min and max of actual sales with the default (reference) table that i was given.

Thank you very much in advance

[amitchandak]

@Dimitris_Kats , Compare in new measure

over all Average Monthly Sales = averageX(Values(Table[Product Type]), [Average Monthly Sales] )

above Avg

if([over all Average Monthly Sales] > [Average Monthly Sales],1,0)

or

Countx(Values([Product Type]), if([over all Average Monthly Sales] > [Average Monthly Sales],[Product Type],blank() ) )

[amitchandak]

@Dimitris_Kats , I think you already have measures  Average Monthly Sales, Max Sales, Min Sales

and you need one over all to compare

averageX(Values(Table[Product Type]), [Average Monthly Sales] )

MAXX(Values(Table[Product Type]), [Max Sales] )

MinX(Values(Table[Product Type]), [Min Sales] )