cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Highest sales recorded against the average

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.

1 ACCEPTED SOLUTION
Community Champion

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)

4 REPLIES 4
Super User

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]))
return SELECTCOLUMNS(FILTER(tbl2,[rank]=1),"date",'Table'[Date])

Proud to be a Super User!

Super User

@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])

Community Champion

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)

Helper III
This is giving me 1 for all the dates.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.