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

@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

10 REPLIES 10
v-kelly-msft
Community Support
Community Support

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!
 

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

 

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!

@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

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/

@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

So happy to have found this response - its taken me a good few hours of different phrases to find the right solution to this problem.  🎉

BA_Pete
Super User
Super User

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


Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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

Try adding a CALCULATE around the SUM, like this:

 

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

 



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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.