Calculated Tables Advice

[Anonymous]

Earlier Opening Opportunity Mon-Fri Summary Table = 
FILTER (
    SUMMARIZECOLUMNS (
        'Earlier Opening Opportunity Mon-Fri Detail Table'[ShopKey],
        "Net Sales Ex Vat", (SUM ( 'Earlier Opening Opportunity Mon-Fri Detail Table'[Net Sales Ex VAT] ))/5),
    [Net Sales Ex Vat] > 40)​

Earlier Opening Opportunity Mon-Fri Detail Table = 
FILTER( 
    FILTER(
        SUMMARIZECOLUMNS (
            DimShop[ShopKey],
            DimDate[WeekOfYear],
            DimDate[DateKey],
            DimDate[DayNameShort],
            CurrentWeekTimes[Opening Time],
            DimTime[TimeSlot],
            "Net Sales Ex VAT", SUM ( FactSalesDetail[Net Sales Ex VAT] )),
        DimDate[DayNameShort] in {"Monday", "Tuesday", "Wednesday", "Thursday", "Friday"}),
    DimTime[TimeSlot] = CurrentWeekTimes[Opening Time])

Hi All

I am creating a Calculated Table from my Main Fact Table (2nd Code Snippet) and then creating a second summary Calculated Table (1st code Snippet) in order to use the ShoKey in the first Table to get a unique count of records.

Can anyone give me any pointers on a potentially easier way to do this, either one Calculated Table or perhaps a single measure to get the Count i require?

Thanks in advance!

[m3tr01d]

@Anonymous 
The questions you need to ask yourself is do you really need to do a Calculated table for your usecase?
The problem is we don't know what you want to do.

90% of the time, you don't need to build a Calculated Table and 95% of the time, you don't even need to know the function SummarizeColumns / Summarize.

- Explain to us the different tables of your model and the relationship

- Give us an example of the final output you want to have (maybe with an excel table)

Then, we can discuss about the possibilities

[AlexisOlson]

I'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 ) )