% of total not calculating correctly at the row level

[manuel_e]

Hello,

I am trying to calculate the difference between "Current Quarter MS%" and "Prior Quarter MS%" but the results are not correct:

Subtotals in gray seem to be calculating correctly but at the row level, they're not. 

My calculations for Current Quarter MS% are:

My calculations for Prior Quarter MS% are:

and to obtain the difference, I have just subtacted one from the other:

But as you can see in the image above, the totals are incorrect, only the subtotals are correct.

Would you please help me?

[manuel_e · Accepted solution]

I found the solution:

I needed to use "ALL" and add the "Manufacturer" column

What this did was that it gave me the totals by subgroup (at the manufacturer level) so the results would be divided by the total of each subgroup (manufacturer) instead of the results being divided by the grand total.

[parry2k]

@manuel_e seems like there is something else that I am missing. If possible share the file, remove any sensitive information before sharing. You can share using onedrive/google drive 

[parry2k]

@manuel_e @How does raw data looks like? How these tables are connected to each other? What column are you using on rows?

[manuel_e]

I have a master table that's related to the current quarter table and the prior quarter table. All of the columns are the same, and I'm using the columns from the master table and including the calculations from the other tables:

I've chosen the columns in the pivot from this master table and all calculations from the other tables. The slicers and filters are really from the master table. 
My table has three rollups:

I've tried using ALL, ALLSELECTED, ALLEXCEPT, a combination of ALL & others but I think I don't understand it well enough. I really just want to subtract one column from the other.

[parry2k]

in your measure allselected should be master table 

[manuel_e]

Thank you, I have just tried it but it gave me the same result. 😞

[parry2k]

@manuel_e 

As a best practice, add a date dimension in your model and use it for time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools. Check the related videos on my YT channel

Add Date Dimension
Importance of Date Dimension
Mark date dimension as a date table - why and how?
Time Intelligence Playlist

[manuel_e]

I do have a date table and they're tied to my tables.