Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
duncanUK
Frequent Visitor

Using measures within a SUMX

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 %.

 

duncanUK_0-1619445186232.png

Thanks for any help

 

 

 

 

 

1 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])

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@duncanUK , Try like

Negative vs Format Avg Stores =

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

Hi @amitchandak 

 

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

@duncanUK , try like

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

Perfect, thank you that worked

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors