cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

Weighted average depending on day of week per category

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:

• a SWITCH measure to define the type of kiosk
• A Switch measure that uses the above to derive a weekend target
• A SWITCH measure that uses the first variable to derive a weekday target
• A variable measure that gets the sales number

Now I am struggling how to put this together in the RETURN section. Would appreciate any tips

2 REPLIES 2
Community Support

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.

Helper I

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

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors