Using measures within a SUMX

[duncanUK]

Hi,

I'm struggling with some DAX and hope somebody can point me in the right direction please.

I'm trying to count the number of stores which are below the average for other stores which are the same Format (e.g. edge of town, shopping centre, high street). The measure works for individual stores, but not at the regional total level.

This is how I calculate the format vs last year average:

Format vs LY % =
DIVIDE (
    CALCULATE ( [Actual Sales Amt], ALLEXCEPT ( Sales, Region[Format_Name] ) )
        - CALCULATE ( [LY Sales Amt], ALLEXCEPT ( Sales, Region[Format_Name] ) ),
    CALCULATE ( [LY Sales Amt], ALLEXCEPT ( Sales, Region[Format_Name] ) )
)

Then I calculate whether the store is below this average value:

Neg_YoY_vs_Format_Store =
IF ( [Var to Last Year %] < [Format vs LY %], 1, 0 )

Then I sum the number of stores which are below average:

Negative vs Format Avg Stores =
IF (
    [LY Sales Amt] > 0,
    IF (
        HASONEVALUE ( Sales[Store_Name] ) = 1,
        [Neg_YoY_vs_Format_Store],
        SUMX ( VALUES ( Sales[Store_Name] ), [Neg_YoY_vs_Format_Store] )
    )
)

I was expecting when using the SUMX it would evaluate the measures for each row in turn, summing the results together. 

The total below should be 7 rather than 1, e.g 7 rows have the Var to Last Year % < Format vs LY %, but it seems it's counting the rows where Var to Last Year % < 69%, which is the regional average Format vs LY %.

Thanks for any help

[amitchandak]

@duncanUK , Try like

Negative vs Format Avg Stores =

SUMX ( VALUES ( Sales[Store_Name] ), if([LY Sales Amt] > 0 , [Neg_YoY_vs_Format_Store],0 ))

[duncanUK]

Hi @amitchandak 

Thanks for looking at this, but unfortunately that gives the same results, with a total of 1 rather than 7.

[amitchandak · Accepted solution]

@duncanUK , try like

SUMX ( summarize ( Sales, Sales[Store_Name] , Sales[format], "_1", if([LY Sales Amt] > 0 , [Neg_YoY_vs_Format_Store],0 )), [_1])

[duncanUK]

Perfect, thank you that worked