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.

```
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
)
)
```