Conditional Formatting Calculation Error When Using Percentages

[GVallentgoed]

Having a problem conditionally formatting my pie graph to be a specific color based on a percetnage range.  In the example provided, a score of >=90% and <95% should produce a yellow result. A score of <90% should product an orange result.  In this example, a score of 90.1% is producing an incorrect orange result.  The issue here is taking an average of all on-time percentages for each month will not produce the same result vs using the raw values.  In this example, using the on-time percentages for each month rather than the raw values is producing an incorrect OTP of 88.9% causing the orange color.   I'm stumped. It's not a basic percision problem, so rounding doesn't help. I can't reference the raw values because they are always changing. As far as I can tell, there's no functionality to make it reference the secondary percent data in brackets.  The only way is to convert my on-time values into percents, but this produces incorrect results due to averaging the percentages.   

[MFelix]

Hi @GVallentgoed ,

For this you need to use a new metric that calculate those values something similar to this:

Colouring = DIVIDE(SUM('Table'[Value]), CALCULATE(SUM('Table'[Value]), ALLSELECTED('Table'[Attribute])))

I'm assuming that your On-time / Late is a column on your data and not the value for each column.

[GVallentgoed]

The issue I'm having with this solution is its creating a separate "total" category that only applies to the total table value.  This is adding additonal values to the data when not selected for "total".  It also breaks if I want to select specific dates in the table.  For example, If I want the on-time percentage for 1 individual SCAC for the 1st quarter of the year (jan, feb, march)  I now need a new total to reference just for those months.  The one you provided is referencing the entire table and not just the 1st quarter. 

[MFelix]

Hi @GVallentgoed ,

Not sure if I understand what you mean by need a new total reference just for those months. The metric I used is based on the ALLSELECTED meaning that only considers the selected period, individuals and so on.

Your data was presented in a single table, are you using dimension tables? Can you explain a little bit better the error you are having implementing this solution.

[GVallentgoed]

Should the formula you provided be inputted into my excel source data or directly into PBI?

[MFelix]

The calculation must be placed in Power bi

[GVallentgoed]

On-Time and Late are are separate columns with their own values. Each value is the sum of the On-time and Late for that SCAC in that month.  Should the On-Time and Late be combined into a single column?  Currently, when I run the formula you provided in both scenerios I get an error ssaying "Too many arguments passed to the SUM function". 

[MFelix]

Hi @GVallentgoed ,

There are two questions on this, the first part has you refer your need to make the unpivot of the On-Time and Late into a single column this can be achieve on power query.

The second part is the error on the measure you have the parentheses on the incorrect place and you are no closing the sum.

Do you know how to do the unpivot or do you need some example in PBIX format?

[GVallentgoed · Accepted solution]

Issue is resolved. I was making it more complicated than it was. I used the Matrix graph as a pivot chart. Created new measure to calculate on-time percetnage and then link conditoinal format to matrix pivot.  Super Simple. I feel dumb. >.< Thanks though!

[GVallentgoed]

when I use unpivot function to put into single column and then apply formula, this is the new result. I don't understand.