cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
duggy Member
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 DateTurnoverMaximum for the dayTuronver to Maximum ValueMedian ValueTurnover to Median ValuePositive Turnover to Median
Store 101-Jun-181001022100.50.50.5
Store 201-Jun-18101 1 -0.5 
Store 301-Jun-18102 0 -1.5 
Store 401-Jun-1895 7 5.55.5
Store 102-Jun-18102102098-4 
Store 202-Jun-1897 5 11
Store 302-Jun-1899 3 -1 
Store 402-Jun-1891 11 77
Store 103-Jun-18104104094.5-9.5 
Store 203-Jun-1893 11 1.51.5
Store 303-Jun-1896 8 -1.5 
Store 403-Jun-1887 17 7.57.5
Store 104-Jun-18106106091-15 
Store 204-Jun-1889 17 22
Store 304-Jun-1893 13 -2 
Store 404-Jun-1883 23 88
Store 105-Jun-18108108087.5-20.5 
Store 205-Jun-1885 23 2.52.5
Store 305-Jun-1890 18 -2.5 
Store 405-Jun-1879 29 8.58.5
Store 106-Jun-18110110084-26 
Store 206-Jun-1881 29 33
Store 306-Jun-1887 23 -3 
Store 406-Jun-1875 35 99
   EXTRA TUROVER TO MAXIMUM275 EXTRA TURNOVER TO MEDIAN56
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Evaluate opportunity to maximum and median of turnover

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png

11 REPLIES 11
hnguy71 Member
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.

 

group_by.PNGgroup_by_result.PNG

duggy Member
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.

hnguy71 Member
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

duggy Member
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.

 

hnguy71 Member
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
Super User

Re: Evaluate opportunity to maximum and median of turnover

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png

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

 

 

duggy Member
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.

hnguy71 Member
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?

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 27 members 800 guests
Please welcome our newest community members: