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

[SBell65]

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.  

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:

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.

[v-kelly-msft]

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!

[SBell65]

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

[v-kelly-msft]

Hi @SBell65 ，

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

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

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!

[SBell65]

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

[Ashish_Mathur]

Hi,

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

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

[StidifordN]

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]

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

[SBell65]

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

Try adding a CALCULATE around the SUM, like this:

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