Calculate Average sales for First Three occurrences by date and the difference b/w Avg - SUM(sales)

[nr_m]

Hi Community,
I'm trying to calculate Avg. sales for first three occurences by date and the diffrence between Avg Sales of First Three occurences, Sum(Sales).
In the below table I'm trying to calculate Baseline(Top 3 Avg)., ie., (30+50+20)/3=33.3 Also trying to get Baseline - Sales for each row.
Please help me to solve this issue. Thanks in advance.

ID	Date	Sales	Baseline (Top3 Avg)	(Baseline - Sales)
1	1-Jan-22	30	33.3	3.3
2	10-Feb-22	50	33.3	16.6
3	13-Mar-22	20	33.3	-13.3
4	4-Apr-22	40	33.3	-6.7
5	8-Sep-22	50	33.3	16.6
Total		190	33.3	16.5

Regards
NR

Please help

@amitchandak @Greg_Deckler 

[MFelix · Accepted solution]

Hi @nr_m 

try the following code:

Baseline - Sales Positive = 
SUMX( FILTER(ADDCOLUMNS(Sales,  "base", [Baseline]), Sales[Sales] <= [base]), [base] - Sales[Sales])

If you want to show the others has 0 then redo the formula to:

Baseline - Sales Positive = 
SUMX( FILTER(ADDCOLUMNS(Sales,  "base", [Baseline]), Sales[Sales] <= [base]), [base] - Sales[Sales] )+0

[MFelix]

Hi @nr_m ,

Create the following two metrics:

Baseline = 
VAR temptable =
    TOPN ( 3,  ALL ( 'Sales'[Date], Sales[Sales], Sales[ID ] ), 'Sales'[Date], ASC )
VAR _result =
    AVERAGEX ( temptable, 'Sales'[Sales] )
RETURN
    _result


Baseline - Sales = 
SUMX( ADDCOLUMNS(Sales,  "base", [Baseline]), [base] - Sales[Sales])

Final result below:

Believe that you have some misscalculation on your example on the last rows.

[nr_m]

Thank you for the Solution @MFelix 
It worked perfectly.
Also I want to display a KPI Score card having Total of only Positive values from Baseline - Sales 
I used If condition and wrote below formulae but it's showing Total as 0.
Can you please check and correct the formulae

Thanks in advance. 
Appriciate your help!

[MFelix · Accepted solution]

Hi @nr_m 

try the following code:

Baseline - Sales Positive = 
SUMX( FILTER(ADDCOLUMNS(Sales,  "base", [Baseline]), Sales[Sales] <= [base]), [base] - Sales[Sales])

If you want to show the others has 0 then redo the formula to:

Baseline - Sales Positive = 
SUMX( FILTER(ADDCOLUMNS(Sales,  "base", [Baseline]), Sales[Sales] <= [base]), [base] - Sales[Sales] )+0

[nr_m]

Hi @MFelix 
Sorry to bother you again, Above solution is working fine on test data but when I applied it on the original data I'm not gettig desired output(baseline). Also have to use category as filter.
I can able to achieve baseline(Top3 Avg) by Group by,

Baseline =

VAR temptable =

    TOPN ( 3, GROUPBY('Table', 'Table'[Date],'Table'[Sales]), 'Table'[Date], ASC )

VAR _result =

    AVERAGEX ( temptable, 'Table'[Sales] )

RETURN

    _result

But showing 0 for baseline-sales

Please find the below screenshot for your refrence also sharing the table data.
Thank you in adavance, Appricaite your support.

ID	Date	Sales	Category
Key-1	1/1/2022	10	A
Key-6	1/1/2022	60	C
Key-7	1/1/2022	70	C
Key-2	2/10/2022	20	A
Key-8	2/10/2022	80	D
Key-9	2/10/2022	90	D
Key-10	2/10/2022	10	B
Key-13	2/10/2022	40	A
Key-3	3/13/2022	30	B
Key-11	3/13/2022	20	D
Key-14	3/13/2022	50	B
Key-4	4/4/2022	40	B
Key-5	9/8/2022	50	C
key-12	9/8/2022	30	A

Regards,

NR

[MFelix · Accepted solution]

Hi @nr_m ,

To what I can understand you want to pick up the values based on the selected categories is that it?

Try the following code:

Baseline = 
VAR temptable =
    TOPN ( 3,  ALLSELECTED(  'Sales'[Date], Sales[Sales], Sales[ID], Sales[Category] ), 'Sales'[Date], ASC )
VAR _result =
    AVERAGEX ( temptable, 'Sales'[Sales] )
RETURN
    _result