Forum Discussion

duncanUK's avatar
duncanUK
Frequent Visitor
5 years ago
Solved

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 ...
  • amitchandak's avatar
    amitchandak
    5 years ago

    duncanUK , try like

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

amitchandak's avatar
amitchandak
Super User
5 years ago

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's avatar
duncanUK
Frequent Visitor
5 years ago

Hi amitchandak 

 

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

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      duncanUK
      Frequent Visitor
      5 years ago

      Perfect, thank you that worked