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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.