Forum Discussion

SBell65's avatar
SBell65
Frequent Visitor
6 years ago
Solved

How to create a measure that sums per attribute value when more than one table is involved

I am trying to create a measure that provides a sum based on each value of a single attribute.  In this case, the "Category" column. The grid in question is shown below and contains data from four di...
Need Help
  • SBell65's avatar
    SBell65
    6 years ago

    v-kelly-msft BA_Pete 

     

    The solution to the measure for this case is as follows:

     

    SumOfSales_Category =

    VAR Cat = MAX(Bookings[Category])

    VAR SumOfCategory = CALCULATE ([Sales (USD)], FILTER (ALLSELECTED ('Bookings' ),  Bookings[Category] = Cat))

    Return SumOfSales_Category

SBell65's avatar
SBell65
Frequent Visitor
6 years ago

BA_Pete   Thanks for the suggestion but unfortunately it doesn't work. I created a measure with your suggestion and the DAX code looks like this:

 

Sales by Category 2 = SUMX(VALUES(Sales[Category]),SUM(Sales[Sales Amount]))

 

This measure returns the exact same value for each row as the measure "Sales (USD)" but the interesting thing is, the grand total is exactly 4 times higher than the "Sales (USD)" measure.  I'm assuming the 4x higher value has to do with the fact that there are four distinct Category values.

 

I then tried an alternate to your original suggestion in the following format:

 

Sales by Category 2 = CALCULATE(SUMX(Sales,Sales[Sales Amount]),VALUES(Sales[Category]))

 

This also didn't work as it, too, gave me the same value per row as the Sales USD measure.  However, the total amount matched the Sales USD measure.

BA_Pete's avatar
BA_Pete
Icon for Super User rankSuper User
6 years ago

Try adding a CALCULATE around the SUM, like this:

 

SalesByCategory =
SUMX(
    VALUES( salestable[Category] ),
    CALCULATE( SUM( salestable[Sales USD] ) )
)