DAX formula assistance to avoid double counting

[allieallie]

Hi all! Trying to ensure that I count each store only once in my categories; right now they're showing up multiple times. Haven't been able to get the distinct count filter to make that distinction for me.

StoreCountByPerformance =

CALCULATE(

    DISTINCTCOUNT('Site Listing'[Longview #]),

    FILTER(

        ADDCOLUMNS(

            SUMMARIZE(

                'Site Listing',

                'Site Listing'[Longview #],

                "RollingAverage", CALCULATE(

                    AVERAGE('Site Listing'[EBIT Actual vs SLAP 1%]),

                    DATESINPERIOD('Site Listing'[Financial Period], LASTDATE('Site Listing'[Financial Period]), -13, MONTH)

                )

            ),

            "Performance_Category",

            SWITCH(

                TRUE(),

                [RollingAverage] >= 0, "Stores meeting or exceeding",

                AND([RollingAverage] < 0, [RollingAverage] >= -0.10), "Stores within 10% miss",

                AND([RollingAverage] < -0.10, [RollingAverage] >= -0.20), "Stores with 11% to 20% miss",

                AND([RollingAverage] < -0.20, [RollingAverage] >= -0.50), "Stores with 21% to 50% miss",

                AND([RollingAverage] < -0.50, [RollingAverage] >= -1.00), "Stores with 50% to 100% miss",

                [RollingAverage] < -1.00, "Stores with greater than 100% miss"

            )

        ),

        [EBIT Per Metric] = "Stores meeting or exceeding" // Change this condition based on the required performance category

    )

)

[rajendraongole1 · Accepted solution]

Hi @allieallie - To calculate only unique stores when applying the performance category filter

slight changes to Dax forumlae: I have adjusted the conditions on filtered table. 

StoreCountByPerformance =
VAR RollingAvgTable =
ADDCOLUMNS(
SUMMARIZE(
'Site Listing',
'Site Listing'[Longview #],
"RollingAverage", CALCULATE(
AVERAGE('Site Listing'[EBIT Actual vs SLAP 1%]),
DATESINPERIOD('Site Listing'[Financial Period], LASTDATE('Site Listing'[Financial Period]), -13, MONTH)
)
),
"Performance_Category",
SWITCH(
TRUE(),
[RollingAverage] >= 0, "Stores meeting or exceeding",
[RollingAverage] < 0 && [RollingAverage] >= -0.10, "Stores within 10% miss",
[RollingAverage] < -0.10 && [RollingAverage] >= -0.20, "Stores with 11% to 20% miss",
[RollingAverage] < -0.20 && [RollingAverage] >= -0.50, "Stores with 21% to 50% miss",
[RollingAverage] < -0.50 && [RollingAverage] >= -1.00, "Stores with 50% to 100% miss",
[RollingAverage] < -1.00, "Stores with greater than 100% miss"
)
)

VAR FilteredTable =
FILTER(
RollingAvgTable,
[Performance_Category] = "Stores meeting or exceeding" // Change this condition based on the required performance category
)

RETURN
CALCULATE(
DISTINCTCOUNT(FilteredTable[Longview #])
)

Check the logic above and let know.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!