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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
BachDinh
Helper I
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: 

 MonTuesdayWednesdayThursdayFridaySaturdaySunday
Kiosk A - Type 1105050808080100

Compare it with a benchmark for Type 1: 

MondayTuesdayWedThuFriSatSun
5050505050100100

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 1102%

 

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
v-rzhou-msft
Community Support
Community Support

Hi @BachDinh ,

 

Here I suggest you to transform your tables by unpivot function. New tables should look like as below.

Actual:

RicoZhou_0-1676622355179.png

Benchmark:

RicoZhou_1-1676622362589.png

Data model:

RicoZhou_2-1676622401308.png

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.

RicoZhou_3-1676622432743.png

 

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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