Issue: Conditional formatting with different target at total level

[Anonymous]

I am trying to do a conditional formatting with different target at different level.

Current Target is set into 2 type:

1.Company level

2.Group level (All company)

Data Point:

Actual Data is from SQL 

Target is from Google sheet (representing monthly target for a whole year)

From below picture, I want to do conditional formatting for each of the row respectively.

Conditional Formatting =

Company's Accuracy% >= Company Target Accuracy%, Green Font

Company's Accuracy% < Company Target Accuracy%, Red Font

Same for Group level

Actual Data.

Period = Year-Month

SKU = SKU code

Forecast = Forecast Qty

Actual = Actual Qty

ABS Error (column formula in BI) = ABS(Forecast-Actual)

Measure Accuracy (measure in BI) = SUM(Abs Error)/SUM(Actual)

[AntonioM]

You can do this by creating a formatting measure, something like:

Accuracy% Formatting = IF( [Measure Accuracy] > [Target Accuracy], "green", "red" )

Then you can use that measure for conditional formatting in your table visual.

You'll need to choose 'Field Value' in the format style and then select the format measure.

That should apply both for company and group level, depending on which you have showing in the table

Any trouble please let me know

[Anonymous]

Can't create a measure because Target is in a column format. Also, both Actual Data and Target are not link together.

I need the conditional formatting to be apply in one table, both Company and Group level too.

[AntonioM]

Ok, is there a way you could create a link between actual and target? We need to try and get to the point where for any row in the table (including the totals) we can get the value of actual, the value of target and then compare them.

[Anonymous]

The link can be with Company column, but has to be both ways.

What I did was Lookupvalue target to the the Actual Data. Then create a measure for that "Lookup Target" but it has to be Average or else it will Sum up.

As you can see the Group Target is not Average of companies. It's a seperate target.

The issue is to map out the "Measure Accuracy%" at group level with the "Target" at Group level.

[AntonioM]

I think you're going to have to use HASONEVALUE() so the measure can work out where in the matrix it is.

HASONEVALUE(company) is true -> use the link to get the target value for the company

HASONEVALUE(company) is false -> ignore the link and get the value for 'Group(A,B,C)'

Something like 

IF(HASONEVALUE(Table[Company]), [Actual] - [Target], [Actual] - CALCULATE([Target], Company = "Group(A,B,C)")

[Anonymous]

I am not sure how to apply this.

How do I proceed with what I have so far? What do I do next for the Data Table or the Target Table?

[AntonioM]

So the first thing I would do is try and create a 'target accuracy' measure, that gets the value you need from your target table.

You said you've used Lookupvalue which should work for the individual companies, but then you'll need to include some logic to change the totals.

Could you share that measure? Im guessing it's something like 

LOOKUPVALUE(
    Target[Target accuracy],
    Target[Company],
    SELETCEDVALUE(Actual[Company])
)

If so, you could try something like

LOOKUPVALUE(
    Target[Target accuracy],
    Target[Company],
    IF (
       HASONEVALUE(Actual[Company]),     //If you're on an individual row
       SELETCEDVALUE(Actual[Company]),   //Find the target for that company
       "Group A,B,C"                     //Find the target for the group
    )
)

[Anonymous]

1.I did Lookup Target to the table. That would result in a SUM target which is wrong.

2.So I created a Measure Lookup Target to by AVERAGE, but that will result in a wrong Group Target (Group Target is 50%) but the Company target matches up. Because Group Target can't be lookup, so the total Target is the average target of companies.

3.I then created a Measure Color Format, and apply conditional formatting by Field Value on "Measure Accuracy%". 

Not sure how can I correct this or make this easier. Currently the outcome is Company Target is an Average and the Group Target can't be fixed (50%)

[AntonioM]

Ok, I'm hoping it's just one change from here. Could you share your entire target table, or is it just the two columns you put in your original post?

If that is the case then I would try

Lookup Target = 
LOOKUPVALUE(Target[Target: Accuracy],Target[Company],
IF(
 HASONEVALUE(Data(Company)),
 Data[Company],
 "Group (A,B,C)"
 )
)

Do you have multiple of each target? If you do is there any way you could link them by period, so that the measure only selects one target percentage at a time and you won't have to use an average?

[Anonymous]

Target is just the 2 column. Currently all period Target is the same.

Although, it still doesn't work.