Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
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.
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
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.
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?
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)
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]
Store | Date | Conversion | Maximum Conversion | Basket Value | Transactions | Shortfall from Maximum | Extra Transactions | Extra Turnover |
Store 1 | 01-Jun-18 | 12% | 14% | 100 | 5 | 2% | 5 | 500 |
Store 2 | 01-Jun-18 | 13% | 14% | 101 | 6 | 1% | 4 | 404 |
Store 3 | 01-Jun-18 | 14% | 14% | 102 | 5 | 0% | 0 | 0 |
Store 4 | 01-Jun-18 | 12% | 14% | 100 | 6 | 2% | 3 | 300 |
Store 1 | 02-Jun-18 | 12% | 15% | 100 | 5 | 3% | 5 | 500 |
Store 2 | 02-Jun-18 | 13% | 15% | 101 | 6 | 2% | 4 | 404 |
Store 3 | 02-Jun-18 | 15% | 15% | 102 | 5 | 0% | 0 | 0 |
Store 4 | 02-Jun-18 | 12% | 15% | 100 | 6 | 3% | 3 | 300 |
Store 1 | 03-Jun-18 | 12% | 16% | 100 | 5 | 4% | 5 | 500 |
Store 2 | 03-Jun-18 | 13% | 16% | 101 | 6 | 3% | 4 | 404 |
Store 3 | 03-Jun-18 | 16% | 16% | 102 | 5 | 0% | 0 | 0 |
Store 4 | 03-Jun-18 | 12% | 16% | 100 | 6 | 4% | 3 | 300 |
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 1 | Jun-18 | 12% | 15% | 100 | 5 | 3% | 5 | 1500 |
Store 2 | Jun-18 | 13% | 15% | 101 | 6 | 2% | 4 | 1212 |
Store 3 | Jun-18 | 15% | 15% | 102 | 5 | 0% | 0 | 0 |
Store 4 | Jun-18 | 12% | 15% | 100 | 6 | 3% | 3 | 900 |
But instead I am getting something along the lines of:
JUNE TOTAL | ||||||||
Store 1 | Jun-18 | 12% | 15% | 100 | 5 | 3% | 5 | 500 |
Store 2 | Jun-18 | 13% | 15% | 101 | 6 | 2% | 4 | 404 |
Store 3 | Jun-18 | 15% | 15% | 102 | 5 | 0% | 0 | 0 |
Store 4 | Jun-18 | 12% | 15% | 100 | 6 | 3% | 3 | 300 |
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.
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).
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
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |