Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to 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)
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])
Proud to be a 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])
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)
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
89 | |
82 | |
76 | |
64 |
User | Count |
---|---|
142 | |
111 | |
108 | |
99 | |
95 |