Weighted average depending on day of week per category

[BachDinh]

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

[v-rzhou-msft]

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.

[BachDinh]

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