How to find Difference between a column and percentage of column total

[mantoshb4u]

In this matrix i have % required value and % of Column Total for sum of actual.

i only have raw data in percentage for Required(Colum 1,its the target value which is given in data) and

the data collected is in numbers for actual(column 2).

i have to find the difference between Required and actual,if it is within +2% or -2% its ok.Otherwise highlight by red.

Please help me with the solution??🙏

[bhanu_gautam · Accepted solution]

@mantoshb4u , Try below steps

Calculate the Column Total: Create a measure to calculate the total of the actual values for each column.

TotalActual = SUM(Table[Actual])

Calculate the Percentage of Column Total: Create a measure to calculate the percentage of the column total for each actual value.

PercentOfTotal = DIVIDE(SUM(Table[Actual]), [TotalActual])

Calculate the Difference: Create a measure to calculate the difference between the required percentage and the percentage of the column total.

Difference = Table[Required] - [PercentOfTotal]
Highlight the Difference: Create a measure to determine if the difference is within the acceptable range (+2% or -2%) and apply conditional formatting.

Highlight = IF(ABS([Difference]) <= 0.02, "OK", "Highlight")

Apply Conditional Formatting:
Go to the table or matrix visual where you want to display the data.
Select the column that shows the difference.
Go to the "Format" pane, then "Conditional formatting".
Choose "Background color" or "Font color" based on your preference.
Add a rule to highlight the cells where the "Highlight" measure is "Highlight" with red color.