Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
mantoshb4u
Regular Visitor

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

mantoshb4u_0-1728310351751.png

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

1 ACCEPTED SOLUTION
bhanu_gautam
Super User
Super User

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




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






View solution in original post

1 REPLY 1
bhanu_gautam
Super User
Super User

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




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.