Subtotal after Measure Calculation doesn't add up

[awolf88]

Dear wizards,

I am - once again - having issue with incorrectly displayed subtotals I was hoping you could help me out with. 

It's a rather complex Data set that I don't wish to change as it functions beautifully up until this point. 

Perhaps I can simplify the problem in a few words and let you have a look at the demo-Data attached:

I have a table displaying different shops with different locations that purchase a mix of different items. I have a calculated measure for Projected total Sales of 2023 (green column). This seems to be aggregating just fine. 

 Next to the green column i have this total Projected 2023 figure split into 3 Categories (1, 2, 3) - displayed in Blue. The values seem to be aggregating just fine per row as they should. Each row for itself adding up to 100%. 

Now for the last calculated columns in orange (Split by category) I break down the value of each row by according percentage (so Projected total * % Category 1 = category 1 split, projected Total * % category 2 = category 2 split, etc.). The values per row seem to aggregating accordingly, but if you have a closer look, you will notice that the subtotals per category split are completely off. 

I would need to fix it within the measure itself if that is anyhow possible. 

Perhaps one of you geniusses has an idea to get me back on the right track...

The one I'm currently going with looks like: 

Here is the link to my demo-data via Dropbox:

https://www.dropbox.com/scl/fo/i6oeb9mvrypo4bawjwov1/h?dl=0&rlkey=vgd213f2hh34sf6aki42s62l7

Thank you so much for your input in advance!

Best,

Alex

[Anonymous · Accepted solution]

Hi @awolf88 ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a measure. 

C1 split =

var _a = SUMMARIZE('BudgetTable',[Customer Name],BudgetTable[Item Name],BudgetTable[Project Name],"Value",CALCULATE([Projected 2023],ALLEXCEPT('Calendar','Calendar'[MonthName]))*MIN('BudgetTable'[Category 1]))

return SUMX(_a,[Value])

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[awolf88]

Hi Neeko,

thank you so much for your assistance with this. Your coding worked like a charm for me.

Best regards and thanks again, 

Alex