SWITCH Statement - Incorrect Grand Total

DAX DAX Calculations
1

Hi Guys,

Struggling with the grand total of a switch statement. I want to compare Footfall for a store to Footfall LY if there is footfall or an AvgYTD (not including current week). The switch statement is showing the correct figures in a table but not as a grand total. I’ve tried the IF(HASONEVALUE) combined with SUMMARISE approach so I can focus on the total but can’t seem to get it right.

DAX Below - I’ve put in a value of 1 to represent where i would like to be a sum of up FootfallLY or FootfallAvgYTDLWWeekly instead of just FootfallAvgYTDLWWeekly.

SWITCH%20Total

Footfall Logic = 
    VAR FootfallLY = [Footfall LY]
    VAR FootfallAvgYTDLWWeekly = DIVIDE(
             CALCULATE(
             [Footfall],
              FILTER(ALL(Vw_DimMBCalendar),
              Vw_DimMBCalendar[YearFlag] = SelectedYear &&
              Vw_DimMBCalendar[Week] < SelectedWeek)
                     ),
       CALCULATE(
            DISTINCTCOUNT(Vw_DimMBCalendar[Week]),
            FILTER(ALL(Vw_DimMBCalendar),
             Vw_DimMBCalendar[YearFlag] = SelectedYear &&
              Vw_DimMBCalendar[Week] < SelectedWeek)
          
            ))
    VAR  FootfallAvgYTDLWDaily =
    DIVIDE(
    CALCULATE(
             [Footfall],
              FILTER(ALL(Vw_DimMBCalendar),
              Vw_DimMBCalendar[YearFlag] = SelectedYear &&
              Vw_DimMBCalendar[Week] < SelectedWeek)    
             ),
       CALCULATE(
            DISTINCTCOUNT(Vw_DimMBCalendar[DayOfYear]),
            FILTER(ALL(Vw_DimMBCalendar),
             Vw_DimMBCalendar[YearFlag] = SelectedYear &&
              Vw_DimMBCalendar[Week] < SelectedWeek)

            )
    )

    VAR Tables = SUMMARIZE(
        Vw_DimMBCalendar,
        Vw_DimMBCalendar[YearFlag],
        Vw_DimMBCalendar[Week],
        "TFootfallLY", FootfallLY,
        "TFootfallLYWTD",FootfallAvgYTDLWWeekly,
        "TFootfallLYWTDDaily",FootfallAvgYTDLWDaily,
        "FootfallLYorLYWTD",
        SWITCH(TRUE(),
        FootfallLY <> BLANK(),FootfallAvgYTDLWWeekly,
        [Footfall LY])
      )
    RETURN
    SWITCH(TRUE(),
    FootfallLY <> BLANK(), -- if there's no footfall LY
    [Footfall LY] --then bring back footfall LY
    -------------------
    ,ISFILTERED(Vw_DimMBCalendar[Short DayName]) -- if a day is selected
    FootfallAvgYTDLWDaily -- then bring back daily
    -------------
    ,IF(HASONEVALUE
         (Vw_DimStores[Storename]),
        FootfallAvgYTDLWWeekly

    ,
    1
    )
    )
2

Just a side note on your formulas.

Personally I don’t like long formulas like this when you can break them out into separate measures. You obviously know what you’re doing as these at least flow nicely and are broken out into variables etc, but it’s still very difficult to run down them and understand what individual part is the problem.

Are you able to move each calc into different measures in this case? It follows my measure branching methodology. Auditing results becomes so much easier when things are down this way.

Especially for me to look at a formula like this is particular difficult as their is a requirement to take into account maybe 7-8 different context that are occurring in the one formula.

Wherever possible I recommend breaking all these variables (and calculations within each variable) into simple DAX measures. Build out piece by piece. Trust me it become so much easier to understand what’s happening.

In any case here’s my attempt to answer this…

…So I’ve been looking through the formula, and unfortunately just so lost.

Way to much going on.

Can you simplify these??

By isolating each specific part of this and breaking it into separate measures working out where the problem is will be so much easier.

Hopefully this is helpful when moving forward on these.

If you want to add your file and can have a look at it further

Thanks
Sam

3

I think maybe the final formula needs to be another SUMMARIZE function instead of in SWITCH. That’s my guess here. But as I say I would simplify the formulas more to truly understand.

Chrs
Sam