I have a table with the turnover of every store per day. Some stores are high, some are low. I am trying to establish how much more in turnover a store could have received if the turnover was as per the maximum of the day or the median of the day.
That is, if 4 stores had turnover of 100, 101, 102 and 103, then the maximum for the day would be 103.
If Store 1 performed as per the maximum, it would have made another 3 in turnover
If Store 2 performed as per the maximum, it would have made another 2 in turnover
If Store 3 performed as per the maximum, it would have made another 1 in turnover
If Store 4 performed as per the maximum, it would have made another 0 in turnover
The total extra turnvover for this day would have been 3+2+1+0= 6 in turonver.
The same for median for each day.
This way we can establish how much more in turnover each store would have made if it performed to the maximum of the days performance or how much more turnover if they would have performed to the median of the day (any stores above median would be 0 in turnover). Does that make sense?
Assuming you have only two columns Date and Turnover, you can get maximum, median, min, etc. turnover by using GroupBy inside Power Query then expanding the rest. Following that would be simple mathematics.
Thanks. What if the table has many more variables and measures? for example there is a measure that establishes the percentage of wi fi users per day. This is a measure rather than a column as we need to exclude wi fi users that are over 100%. Can the operation described above still be carried out?
Another issue that I have is that the table is a created table by pulling data from other tables. Not a clean data table pulled from the database. So for example the Turnover is a Lookup from the turnover table matching the date and storeid. The Wi Fi daily usage is pulled from the WiFi table in the same manner etc. The Group By seems to only function in the Edit Data portion where you pull data from the database.
Going back to the original spreadsheet, I have multiple stores in a given day with multiple turnover results. I want to get the maximum turnover achieved across all the stores for the day (i.e. the highest turnover acheived for that day) and then to calculate for each store a value of how far it was from the maximum. So each store would ahve another column for distance from maximum, the store with the highest turnover for the day would result in 0 in the distance from maximum.
I need it to calculate this per day (i.e. for each day which store had the highest revenue and then the shortfalls of the other stores). At the moment if I filter the information for a single day, its magic, if I add more days to the filter it takes the sum of all the days and calculates it that way.
Each day a store performed the best in revenue, how far were all the other stores from this store (how much did they have to earn each day to equate to the highest performing store of that day). And then for the period what is the total that each store could have earned had it equated to the best performing store of the day (day by day).
Does that make sense?
Once thats done I need to do the same but for the median. So the stores below the median (the bottom performing stores) should they have performed at the median then they would have earned X amount for each day (in total they would have earned Y).
Max Turnover = CALCULATE(MAX(Sheet1[Turnover]), FILTER(Sheet1, Sheet1[Date]=EARLIER(Sheet1[Date])))
Seems to do the trick however (and possibly something I should have stated before), I use Roles and filter the stores based on a seperate table where the user is assigned to his stores. So the formula above does indeed return the maximum for the given day for ALL stores. When I add the 'View as Role' and only the specific user (where only his 4 stores apear out of the 100 stores), the maximum still comes out as the maximum for the day for ALL stores not the maximum for the 4 stores that he is allocated to.
The two solutions posted should do the trick. I was able to swap role views and see only the max turnover for the day that "Bob" was able to see only. Are you setting up your roles properly? Are you changing the dax formulas?