Forum Discussion
Anonymous
4 years agoNot applicable
Calculated Tables Advice
Earlier Opening Opportunity Mon-Fri Summary Table =
FILTER (
SUMMARIZECOLUMNS (
'Earlier Opening Opportunity Mon-Fri Detail Table'[ShopKey],
"Net Sales Ex Vat", (SUM ( 'Earlier ...
AlexisOlson
Super User
4 years agoI'm pretty confident that there's a cleaner way but, working with what I see, I'd suggest exploring a solution along these lines:
Earlier Opening Opportunity Mon-Fri Count =
VAR Summary =
FILTER (
SUMMARIZECOLUMNS (
DimShop[ShopKey],
CurrentWeekTimes[Opening Time],
DimTime[TimeSlot],
TREATAS (
{ "Monday", "Tuesday", "Wednesday", "Thursday", "Friday" },
DimDate[DayNameShort]
),
"@NetSalesExVAT", CALCULATE ( SUM ( FactSalesDetail[Net Sales Ex VAT] ) )
),
DimTime[TimeSlot] = CurrentWeekTimes[Opening Time]
)
VAR Grouped =
GROUPBY (
Summary,
DimShop[ShopKey],
"@ShopSales", SUMX ( CURRENTGROUP (), [@NetSalesExVAT] )
)
RETURN
COUNTROWS ( FILTER ( Grouped, ( [@ShopSales] / 5 ) > 40 ) )