Highest sales recorded against the average

[Ethanhunt123]

I have a table that has Order, date, Sales

Order #		Date		Sales
Order #1		20 Aug 2019		100
Order #2		20 Aug 2019		200
Order #3		19 Aug 2019		100
Order #4		19 Aug 2019		100
Order #5		19 Aug 2019		200
Order #6		18 Aug 2019		200

First I have to find the average sales per day . So for 20 AUG 2019 average would be 150, 19 AUG 2019  = 133 and for 18 AUG average would be 200.

Then I have to find highest sales against the average 

20 AUG 2019 ( 200 - 150) = 50

19 AUG 2019 (200 - 133) = 67

18 AUG 2019 ( 200- 200)  = 0 

So the output would be 19 AUG 2019. Many dates can have 67 so I want all those dates as output.

[ryan_mayu]

@Ethanhunt123 

you can try to create a new table to return the date value.

Table 2 = 
VAR tbl=SUMMARIZE('Table','Table'[Date],"diff",max('Table'[Sales])-AVERAGE('Table'[Sales]))
VAR tbl2=ADDCOLUMNS(tbl,"rank",RANKX(tbl,[diff],,DESC))
return SELECTCOLUMNS(FILTER(tbl2,[rank]=1),"date",'Table'[Date])

[amitchandak]

@Ethanhunt123 , Plot this measure with date

countx(filter(summarize(Table,table[Date], "_1", Rankx(all(Table), calculate( max(Table[Sales]) - avergage(Table[Sales])),,desn,skip)),[_1]=1),[Date])

[harshnathani · Accepted solution]

Hi @Ethanhunt123 ,

Create these measures

Average Sale = 
AVERAGE('Table'[Sales])

MAXX Sale = MAXX(FILTER(ALL('Table'),'Table'[Date] = MAX('Table'[Date])),'Table'[Sales])

Highest Sale against Average = [MAXX Sale] - [Average Sale]

Rank Highest Sales = RANKX(ALL('Table'[Date]), [Highest Sale against Average])

Create a Visual Filter where Rank =1

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Ethanhunt123]

This is giving me 1 for all the dates.