Subtraction of 2 percentage columns with conditional formatting

[hkrash0920]

Hi...I am trying to subtract two different columns that don't sum the total. See below.

The MMT Diff column is a new column -  MMT Diff = [Current Margin]-[MMT Margin]

The MMT Margin and Current Margin columns are percentages.

The Total Row is adding up all the above percentages. I want it to be the same as above to be [Current Margin]-[MMT Margin].

This is just a portion of the table to which the Total row doesn't match with the picture. 🙂

I assume a measure needs to be created but I can't figure out how.

Please help.

Thanks

[GabrielSantos · Accepted solution]

Please try this:

Define sums of your MMT and Current Margin with :

=SUM(Table[MMT Margin])

=SUM(Table1[Current Margin])

Next, determine the count of your item with :

COUNTROWS(Table1)

These two will enable you to create a weighted average to determine the MMT Diff margin. Create a measure with the following:

([Sum MMT Margin]-[Sum Current Margin])/[Count]

Finally, to get a PivotTable display that makes sense, you're going to want to place the average percentages with the following:

AVERAGE(Table1[MMT Margin])

AVERAGE(Table1[Current Margin])

Place the Average measures and the MMT Diff measure on your PivotTable to yield the following: