Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am trying to achieve a comparison to benchmark of how many products did a store sell compared to the benchmark. The benchmark is dependent on the type of the kiosk and the day of the week.
So the expected output is the average of each the sales/benchmark for the entire week (averaging all 7 days, 2 of which have a different benchmark as they are weekend days), per kiosk.
So in steps the formula would take the output per weekday:
Mon | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday | |
Kiosk A - Type 1 | 10 | 50 | 50 | 80 | 80 | 80 | 100 |
Compare it with a benchmark for Type 1:
Monday | Tuesday | Wed | Thu | Fri | Sat | Sun |
50 | 50 | 50 | 50 | 50 | 100 | 100 |
So for each weekday we would have in order, 20%, 100%, 100%, 160%, 160%, 80% amd 100%
And the final output would be the average of these numbers = 102%
Output | |
Kiosk A - Defined as type 1 | 102% |
So far I have 4 variables:
Now I am struggling how to put this together in the RETURN section. Would appreciate any tips
Hi @BachDinh ,
Here I suggest you to transform your tables by unpivot function. New tables should look like as below.
Actual:
Benchmark:
Data model:
Measure:
Average =
VAR _SUMMARIZE =
SUMMARIZE (
Benchmark,
Benchmark[Type],
Benchmark[Weekday Num],
"Percentage",
VAR _Benchmark =
CALCULATE ( SUM ( Benchmark[Value] ) )
VAR _Actual =
CALCULATE (
SUM ( Actual[Value] ),
FILTER (
Actual,
Actual[Type] = EARLIER ( Benchmark[Type] )
&& Actual[WeekDay Num] = EARLIER ( Benchmark[Weekday Num] )
)
)
RETURN
DIVIDE ( _Actual, _Benchmark )
)
RETURN
DIVIDE ( SUMX ( _SUMMARIZE, [Percentage] ), 7 )
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Rico,
I actually arrived at a similar solution that unluckily isn't working properly 100% of the time.
I used a GENERATE() function to calculate a VAR table that is unpivoted and in similar structure to what you showed, but retrieval of data from it seems to be problematic at times with no clear rule as to when the issue happens, detailed further at my post here
Would appreciate if you could take a look.
Thanks for the help so far,
B
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
147 | |
109 | |
107 | |
85 | |
63 |