Multiple dynamic filters


#1

Hello there,

I am calculating total sales. sounds simple. but filters that i need in sales amount is 1) Sales YTD 2) based on sales type selection 4) and based on store category selection. This seems to be working file. But is there a simpler way to achieve the result.

Total Sales =
VAR CurrentYear = [Sales Year]
VAR StoreTypeSelection =
    IF (
        HASONEVALUE ( 'store comparison flag'[Select Measure] ),
        SELECTEDVALUE ( 'store comparison flag'[Select Measure] ),
        "All"
    )
VAR SalesTypeSelection =
    IF (
        HASONEVALUE ( 'sales type'[Sales Type] ),
        SELECTEDVALUE ( 'sales type'[Sales Type] ),
        "All"
    )
VAR TotalAmount =
    CALCULATE (
        IF (
            ISFILTERED ( 'sales type'[Sales Type] ),
            CALCULATE (
                SUM ( 'sales'[Sales Amount] ),
                FILTER ( 'sales', 'sales'[Sale Type] = SalesTypeSelection )
            ),
            CALCULATE ( SUM ( 'sales'[Sales Amount] ) )
        ),
        FILTER ( 'calendar', 'calendar'[Sales Year] = CurrentYear )
    )
RETURN
    IF (
        ISFILTERED ( 'store comparison flag'[Select Measure] ),
        CALCULATE (
            CALCULATE (
                IF (
                    ISFILTERED ( 'sales type'[Sales Type] ),
                    CALCULATE (
                        SUM ( 'sales'[Sales Amount] ),
                        FILTER ( 'sales', 'sales'[Sale Type] = SalesTypeSelection )
                    ),
                    CALCULATE ( SUM ( 'sales'[Sales Amount] ) )
                ),
                FILTER ( 'calendar', 'calendar'[Sales Year] = CurrentYear )
            ),
            FILTER (
                stores,
                COUNTROWS (
                    FILTER (
                        'store comparison flag',
                        stores[Stores Comparision Flag] = StoreTypeSelection
                    )
                )
            )
        ),
        TotalAmount
    )

#2

I must say this does seem very complicated, but you are placing a lot of logic inside here with the selected measure.

Personally I rarely have formula this way. I always aim for the simplest formula I can possibly create and use ‘measure branching’ technique where ever possible.

My concern here is that would anyone be able to audit the results here. Even I would struggle to be honest. There’s just way to much going on inside of one formula.

One thing to do it maybe break out some of the variable into there own measures. That a quick win.

Maybe that’s all that’s really possible on looking over this a few times.

If it works it works, that’s great. Just make sure to maybe add some comments to help others understand it also.

Chrs