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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Perfect, thank you that worked

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.