cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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 different tables, as represented by the header colors. The "Sales (USD)" is the only existing measure column.  The measure I'm trying to create in Power BI is in yellow.  

PO Measure 1.jpg

The end user wants this data filtered by Year where Year=2020 and Business Unit is (FLD, LVM, SSB).  These two filter criteria are based on attributes that do not come from the Sales table, so the new measure needs to work properly with this filter criteria. The Date, Customer, and Product tabes have a 1-to-many relationship with the Sales table.

 

The end user wants the final table to look like this:

 

PO Measure 2.jpg

 

I created a measure with the following DAX code and it works perfectly IF all columns of data reside in a single table, which is not the case for my environment.

 

Sales by Category (USD) = CALCULATE (SUM (Sales[Sales Amount]), ALLEXCEPT (Sales,Sales[Category]))

 

I also tried changing the DAX code for this measure to include the other tables involved in an attempt for it to work properly.  I tried this measure definition as well.

 

Sales by Category (USD) = CALCULATE (SUM (Sales[Sales Amount]), ALLEXCEPT (Sales,Sales[Category],Date,Customer,Products))

 

That did not work either. When I applied the first filter of Year=2020, the grid exploded in size, generating one row for every distinct Year value per row that already existed in the grid.  As though it was doing an outerjoin or something.

 

Should I be using something different than the ALLEXCEPT function to build this new measure?  What DAX code would work as needed to produce that final result table given the filter requirements?  The end user wants to filter on the new measure to see only those Categories that are at least $1,000,000 in value.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

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

@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

View solution in original post

9 REPLIES 9
Highlighted
Super User II
Super User II

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

Hi @SBell65

I'm not at a computer so can't test, but something like the following measure should work:

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

Pete
Highlighted
Frequent Visitor

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

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

Highlighted
Super User II
Super User II

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

Try adding a CALCULATE around the SUM, like this:

 

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

 

Highlighted
Community Support
Community Support

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

Hi @SBell65 ,

 

I guess the issue happens in the measure of [Sales Amount],based on the data you provided,I feel a bit difficult to get the output,so if it's available,could you pls upload your .pbix to onedrive business and share it with us?Remember to remove the confidential information.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
 
Highlighted
Frequent Visitor

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

@v-kelly-msft 

I have my Power BI report ready and I've put it out on my OneDrive, in a subfolder I just created for this .pbix file.  However, I'm not sure how to share it with you.  If you could please advise how I go about doing that. 

Thank you.

 

Highlighted
Community Support
Community Support

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

Hi @SBell65

 

Right click on the .pbix file in onedrive then choose "share":

1.png

Then choose "anyone with the link">"apply":

2.png

 

Finally you will get a link,just share the link with us.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Highlighted
Frequent Visitor

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

@v-kelly-msft 

 

Thank you for the reply. Unfortunately, our company has secured that function and is not available for use.  Is there any other option available to me to get you my .pbix file?

 

Share Fail.jpg

Highlighted
Frequent Visitor

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

@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

View solution in original post

Highlighted
Super User V
Super User V

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

Hi,

Put it up on Google Drive and share the expected result very clearly.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors