Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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??🙏
Solved! Go to 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.
Proud to be a 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.
Proud to be a Super User! |
|
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
84 | |
69 | |
68 | |
39 | |
37 |