Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
duggy
Advocate II
Advocate II

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

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

11 REPLIES 11
hnguy71
Memorable Member
Memorable Member

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



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

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

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



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

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

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?



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

Hi,

 

When the roles are used ony the stores that they are allocated to are visible. The data only shows the data fro their stores but the formula is showing maximum for all the stores, not just the ones they are selected for.

 

see below (with roles turned on)

wrong.JPG

 

without roles.JPG

 

As you can see when the roles are active we are still getting the maximum from all stores as if the roles are not active.

 

 

 

 

So I have something working when we filter down per day.

That is I am able to get the maximum for the day between stores, I am able to see the shortfall per store with regards to the maximum and therefore evalute the shortfall for turnover.

 

However when I drill up to monthly or yearly then it doesnt add up all the turnover for the above per day, but rather reevaluates oll the above and gives me a much lower turnover.

 

Usually I work with Calculated colums so that the values are added in totals, here meaures were required and that seems to have messed up the totals.

 

This meaure is causing the issue (not working it out per day but rather on totals):

New Increased Turnover Based on Maximum Conversion = [Increased Transactions based on Maximum Conversion Shortfall] * [Average Basket Value Measure]
 
Might be easier if I drop some dummy data here to highlight my issue:
StoreDateConversionMaximum ConversionBasket ValueTransactionsShortfall from MaximumExtra TransactionsExtra Turnover
Store 101-Jun-1812%14%10052%5500
Store 201-Jun-1813%14%10161%4404
Store 301-Jun-1814%14%10250%00
Store 401-Jun-1812%14%10062%3300
Store 102-Jun-1812%15%10053%5500
Store 202-Jun-1813%15%10162%4404
Store 302-Jun-1815%15%10250%00
Store 402-Jun-1812%15%10063%3300
Store 103-Jun-1812%16%10054%5500
Store 203-Jun-1813%16%10163%4404
Store 303-Jun-1816%16%10250%00
Store 403-Jun-1812%16%10064%3300

 

The above is per day and working fine.

The below is when you drill up to the month and the totals should be as follows:

JUNE TOTAL       
Store 1Jun-1812%15%10053%51500
Store 2Jun-1813%15%10162%41212
Store 3Jun-1815%15%10250%00
Store 4Jun-1812%15%10063%3900

 

But instead I am getting something along the lines of:

JUNE TOTAL       
Store 1Jun-1812%15%10053%5500
Store 2Jun-1813%15%10162%4404
Store 3Jun-1815%15%10250%00
Store 4Jun-1812%15%10063%3300

 

The code:

 

Maximum Conversion = MAXX(ALL('Sites'[Store]),[Conversion])
Shortfall from Maximum = [Maximum conversion] - [Conversion]
Extra Transactions = [Visitors for day] * ([Shortfall from Maximum]/100)
Extra Turnover = [Extra Transactions] * [Average Basket Value]

 

So it is calculating the totals on a per line basis rather than the sum of the per day table above like it would for calculated columns rather than measures.

 

I am going in circles. Any ideas?

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.

 

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

 

hnguy71
Memorable Member
Memorable Member

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



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.