Forum Discussion

allieallie's avatar
allieallie
New Member
2 years ago
Solved

DAX formula assistance to avoid double counting

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

    )

)

 

 

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    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!!

1 Reply

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    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!!