cancel
Showing results for
Did you mean:
Member

## Evaluate opportunity to maximum and median of turnover

Hi,

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?

 Store Date Turnover Maximum for the day Turonver to Maximum Value Median Value Turnover to Median Value Positive Turnover to Median Store 1 01-Jun-18 100 102 2 100.5 0.5 0.5 Store 2 01-Jun-18 101 1 -0.5 Store 3 01-Jun-18 102 0 -1.5 Store 4 01-Jun-18 95 7 5.5 5.5 Store 1 02-Jun-18 102 102 0 98 -4 Store 2 02-Jun-18 97 5 1 1 Store 3 02-Jun-18 99 3 -1 Store 4 02-Jun-18 91 11 7 7 Store 1 03-Jun-18 104 104 0 94.5 -9.5 Store 2 03-Jun-18 93 11 1.5 1.5 Store 3 03-Jun-18 96 8 -1.5 Store 4 03-Jun-18 87 17 7.5 7.5 Store 1 04-Jun-18 106 106 0 91 -15 Store 2 04-Jun-18 89 17 2 2 Store 3 04-Jun-18 93 13 -2 Store 4 04-Jun-18 83 23 8 8 Store 1 05-Jun-18 108 108 0 87.5 -20.5 Store 2 05-Jun-18 85 23 2.5 2.5 Store 3 05-Jun-18 90 18 -2.5 Store 4 05-Jun-18 79 29 8.5 8.5 Store 1 06-Jun-18 110 110 0 84 -26 Store 2 06-Jun-18 81 29 3 3 Store 3 06-Jun-18 87 23 -3 Store 4 06-Jun-18 75 35 9 9 EXTRA TUROVER TO MAXIMUM 275 EXTRA TURNOVER TO MEDIAN 56
1 ACCEPTED SOLUTION

Accepted Solutions
Super User

Hi,

Hope this helps.

11 REPLIES 11
Member

## Re: Evaluate opportunity to maximum and median of turnover

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.

Member

## Re: Evaluate opportunity to maximum and median of turnover

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.

Member

## Re: Evaluate opportunity to maximum and median of turnover

I'll need to understand more in-depth of your tables where you're piecing data together but it's possible to get the same results without going into the power query.

You can do a simple Max function which gets aggregated by date:

`MaxTurnoverMeasure = MAX(Sheet1[Turnover])`

or even using GroupBy in a new table:

`GroupMe = GROUPBY(Sheet1, Sheet1[Date], "MaxTurnover", MAXX(CURRENTGROUP(), Sheet1[Turnover]))`

or a calculated column using max and earlier:

`Max Turnover = CALCULATE(MAX(Sheet1[Turnover]), FILTER(Sheet1, Sheet1[Date]=EARLIER(Sheet1[Date])))`

Hope this helps you to moving forward

Member

## Re: Evaluate opportunity to maximum and median of turnover

None of this is working for me.

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.

Member

## Re: Evaluate opportunity to maximum and median of turnover

I've uploaded a sample PBI. Can you tell me if it's what you're looking for?

The regular columns are built vis Power Query. The columns with "cc" in front are created via calculated columns. I've also included the GroupBy outside of PowerQuery for you to check out.

https://1drv.ms/u/s!An8CCFsOzw0uaW3ygYOWidPBowY

Super User

Hi,

Hope this helps.

Member

## Re: Evaluate opportunity to maximum and median of turnover

Hi,

This is almost perfect.

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).

Member

## Re: Evaluate opportunity to maximum and median of turnover

`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.

Member

## Re: Evaluate opportunity to maximum and median of turnover

Hi duggy.

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?

Announcements

Power BI Super User, Greg Deckler, explains

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 27 members 800 guests
Recent signins: