Define colors for 3 KPI for each zones

[majid154a]

helpDear Experts,

I have been struggling for a long time to solve my problem, and I haven't found a solution yet.

Problem Description:

I have delivery company data that includes zones and the following key indicators:

• Average order time
• Average vendor acceptance time
• Average delivery time to the customer

Challenge:

I want to color the three indicators in green and red based on achieving the target or not, noting that the target for each zone is different.

Target details by zones:

• In Zone A:

  • Average order time: <= 35 minutes (Green), less than that (Red)
  • Average vendor acceptance time: <= 3 minutes (Green), less than that (Red)
  • Average delivery time to the customer: <= 15 minutes (Green), less than that (Red)

• In Zone B:

  • Average order time: <= 30 minutes (Green), less than that (Red)
  • Average vendor acceptance time: <= 4 minutes (Green), less than that (Red)
  • Average delivery time to the customer: <=20 minutes (Green), less than that (Red)

• In Zone C:

  • Average order time: <= 40 minutes (Green), less than that (Red)
  • Average vendor acceptance time: <=5 minutes (Green), less than that (Red)
  • Average delivery time to the customer: <= 25 minutes (Green), less than that (Red)
    
    I need to display a matrix chart with zones in the rows and the three KPIs in the values.

I kindly request your assistance in solving this problem and dynamically applying the coloring based on the specified conditions for each zone.

Thank you in advance for your cooperation.


Attached Data as example:

Zones	total order time	vendor Acceptance time	arrival at customer time
A	37	4	24
B	38	5	27
B	22	5	28
C	27	2	23
A	26	2	10
A	40	1	14
C	28	4	12
C	31	3	27
A	17	1	25
B	39	2	10
B	22	4	29
C	33	3	19
A	23	7	17
A	16	2	25
C	32	7	15
C	39	1	25
A	33	4	16
A	22	1	12
C	36	3	29
C	32	7	18
A	29	3	29
B	38	2	16
B	36	5	29
C	31	1	11
A	40	7	26

[Irwan · Accepted solution]

hello @majid154a 

please check if this accomodate your need.

1. Create a measure for 'total order time' conditional formating.

Order Color Palette =

var _Zone = SELECTEDVALUE('Table'[Zones])

var _OrderTime = SELECTEDVALUE('Table'[ total order time])

Return

IF(

    _Zone="A"&&_OrderTime<=35, //Zone A and Order Time less than or equal 35

    "#00FF00", //Green Code

IF(

    _Zone="B"&&_OrderTime<=30, //Zone B and Order Time less than or equal 30

    "#00FF00", //Green Code

IF(

    _Zone="C"&&_OrderTime<=40, //Zone C and Order Time less than or equal 40

    "#00FF00", //Green Code

    "#FF0000" //Red Code

)))

2. Create a measure for 'vendor acceptance time' conditional formating.

Vendor Color Palette =

var _Zone = SELECTEDVALUE('Table'[Zones])

var _VendorTime = SELECTEDVALUE('Table'[vendor Acceptance time])

Return

IF(

    _Zone="A"&&_VendorTime<=3, //Zone A and Vendor Time less than or equal 3

    "#00FF00", //Green Code

IF(

    _Zone="B"&&_VendorTime<=4, //Zone B and Vendor Time less than or equal 34

    "#00FF00", //Green Code

IF(

    _Zone="C"&&_VendorTime<=5, //Zone C and Vendor Time less than or equal 5

    "#00FF00", //Green Code

    "#FF0000" //Red Code

)))

3. Create a measure for 'arrival at customer time' conditional formating.

Customer Color Palette =

var _Zone = SELECTEDVALUE('Table'[Zones])

var _CustomerTime = SELECTEDVALUE('Table'[ arrival at customer time])

Return

IF(

    _Zone="A"&&_CustomerTime<=15, //Zone A and Customer Time less than or equal 15

    "#00FF00", //Green Code

IF(

    _Zone="B"&&_CustomerTime<=20, //Zone B and Customer Time less than or equal 20

    "#00FF00", //Green Code

IF(

    _Zone="C"&&_CustomerTime<=25, //Zone C and Customer Time less than or equal 25

    "#00FF00", //Green Code

    "#FF0000" //Red Code

)))

4. Plot your data, hit that arrow in 'total order time' Columns value, select Conditional Formatting, then pick conditional formatting you want (background or font color). I picked background color in this case.

5. Hit Format Style and choose Field Value

6. Choose your table, then select the 'Order Colot Palette" measure that previously made for 'total order time'. then OK.

7. Do exact same way for 'vendor acceptance time' and 'arrival at customer time'.

You have to make sure to select vendor color measure for 'vendor acceptance time' and customer color measure for 'arrival at customer time'.

Hope this will help you.

Thank you.

[Irwan · Accepted solution]

hello @majid154a 

please check if this accomodate your need.

1. Create a measure for 'total order time' conditional formating.

Order Color Palette =

var _Zone = SELECTEDVALUE('Table'[Zones])

var _OrderTime = SELECTEDVALUE('Table'[ total order time])

Return

IF(

    _Zone="A"&&_OrderTime<=35, //Zone A and Order Time less than or equal 35

    "#00FF00", //Green Code

IF(

    _Zone="B"&&_OrderTime<=30, //Zone B and Order Time less than or equal 30

    "#00FF00", //Green Code

IF(

    _Zone="C"&&_OrderTime<=40, //Zone C and Order Time less than or equal 40

    "#00FF00", //Green Code

    "#FF0000" //Red Code

)))

2. Create a measure for 'vendor acceptance time' conditional formating.

Vendor Color Palette =

var _Zone = SELECTEDVALUE('Table'[Zones])

var _VendorTime = SELECTEDVALUE('Table'[vendor Acceptance time])

Return

IF(

    _Zone="A"&&_VendorTime<=3, //Zone A and Vendor Time less than or equal 3

    "#00FF00", //Green Code

IF(

    _Zone="B"&&_VendorTime<=4, //Zone B and Vendor Time less than or equal 34

    "#00FF00", //Green Code

IF(

    _Zone="C"&&_VendorTime<=5, //Zone C and Vendor Time less than or equal 5

    "#00FF00", //Green Code

    "#FF0000" //Red Code

)))

3. Create a measure for 'arrival at customer time' conditional formating.

Customer Color Palette =

var _Zone = SELECTEDVALUE('Table'[Zones])

var _CustomerTime = SELECTEDVALUE('Table'[ arrival at customer time])

Return

IF(

    _Zone="A"&&_CustomerTime<=15, //Zone A and Customer Time less than or equal 15

    "#00FF00", //Green Code

IF(

    _Zone="B"&&_CustomerTime<=20, //Zone B and Customer Time less than or equal 20

    "#00FF00", //Green Code

IF(

    _Zone="C"&&_CustomerTime<=25, //Zone C and Customer Time less than or equal 25

    "#00FF00", //Green Code

    "#FF0000" //Red Code

)))

4. Plot your data, hit that arrow in 'total order time' Columns value, select Conditional Formatting, then pick conditional formatting you want (background or font color). I picked background color in this case.

5. Hit Format Style and choose Field Value

6. Choose your table, then select the 'Order Colot Palette" measure that previously made for 'total order time'. then OK.

7. Do exact same way for 'vendor acceptance time' and 'arrival at customer time'.

You have to make sure to select vendor color measure for 'vendor acceptance time' and customer color measure for 'arrival at customer time'.

Hope this will help you.

Thank you.

[majid154a]

Hi @Irwan 

I tested this code, and it showed me color codes instead of numbers.
I have attached the code used for your reference.

[Irwan]

Hello @majid154a 

following my post replying your question above, you place your column into matrix column value not those measures you made.

These measures are used to assign color based on average value of your column ('total order time', 'vendor acceptance time', and 'arrival at customer time'). So do not place those measure for your matrix column value.

1. you place your column value in matrix visual as below image (use average value).

2. hit that down arrow to assign color based on measures you made before.

3. select Field Value in Format Style and choose the measure based on the column you are working on.

for example, I tried to assign color to 'total order time' and so i choose measure 'Order Color Palette' because 'Order Color Palette' has conditional if color for 'total order time'

4. and do exact same way for 'vendor acceptance time' and 'arrival at customer time'.

Hope this will help you.

Thank you.

[majid154a]

Thank you @Irwan  very much for your kind help.

You used SELECTEDVALUE in the three measures because it is applied to a column. How can I then convert it to the average for each of the three measures, as the requirement is for the average, as I mentioned in the question?

Also, zones should appear only once for each zone (don't summarize). The numbers will change based on the slicers available.

Also, can I use icons instead of backgrounds?
 

I greatly appreciate your assistance and am grateful for your cooperation.

Is it possible to send the file in which you solved the example?

Thank you again.

[Irwan]

Hello @majid154a ,

those measures are used for defining color based on conditional if.

Yes, you can choose many option for conditional formating.

Ah sorry, I misunderstood your goal.

Here is the update. I have changed into matrix visual so zones are appear only once.

Here is the DAX for measure to assign average value : 

Customer Color Palette =

var _Zone = SELECTEDVALUE('Table'[Zones])

var _CustomerTime = SELECTEDVALUE('Table'[ arrival at customer time])

Return

IF(

    _Zone="A"&&AVERAGE('Table'[ arrival at customer time])<=15, //Zone A and Customer Time less than or equal 15

    "#00FF00", //Green Code

IF(

    _Zone="B"&&AVERAGE('Table'[ arrival at customer time])<=20, //Zone B and Customer Time less than or equal 20

    "#00FF00", //Green Code

IF(

    _Zone="C"&&AVERAGE('Table'[ arrival at customer time])<=25, //Zone C and Customer Time less than or equal 25

    "#00FF00", //Green Code

    "#FF0000" //Red Code

)))

Order Color Palette =

var _Zone = SELECTEDVALUE('Table'[Zones])

var _OrderTime = SELECTEDVALUE('Table'[ total order time])

Return

IF(

    _Zone="A"&&AVERAGE('Table'[ total order time])<=35, //Zone A and Order Time less than or equal 35

    "#00FF00", //Green Code

IF(

    _Zone="B"&&AVERAGE('Table'[ total order time])<=30, //Zone B and Order Time less than or equal 30

    "#00FF00", //Green Code

IF(

    _Zone="C"&&AVERAGE('Table'[ total order time])<=40, //Zone C and Order Time less than or equal 40

    "#00FF00", //Green Code

    "#FF0000" //Red Code

)))

Vendor Color Palette =

var _Zone = SELECTEDVALUE('Table'[Zones])

var _VendorTime = SELECTEDVALUE('Table'[vendor Acceptance time])

Return

IF(

    _Zone="A"&&AVERAGE('Table'[vendor Acceptance time])<=3, //Zone A and Vendor Time less than or equal 3

    "#00FF00", //Green Code

IF(

    _Zone="B"&&AVERAGE('Table'[vendor Acceptance time])<=4, //Zone B and Vendor Time less than or equal 34

    "#00FF00", //Green Code

IF(

    _Zone="C"&&AVERAGE('Table'[vendor Acceptance time])<=5, //Zone C and Vendor Time less than or equal 5

    "#00FF00", //Green Code

    "#FF0000" //Red Code

)))

Change column value into average (default is sum). and do this for all your column value ('total order time', 'vendor acceptance time', and 'arrival at customer time')

Please check this link for pbix download.

https://www.dropbox.com/scl/fi/poc630a39nuer73mprhqb/4039971-M160184.pbix?rlkey=zrg18mejsc0evpoddck2... 

Hope this will help you.

Thank you.

[majid154a]

Thank you @Irwan ,

dear brother. I have tried the solution, and it worked correctly for me. I just need to know how to make the indicators appear as icons, as shown in the attachment, instead of the background.

Tnank you again in advanced @Irwan 

[Irwan · Accepted solution]

hello @majid154a 

glad to be a help.

select icon if you want to have icon conditional formating.

1. Change the color code into number. This is for measure average total order, do exact same way for measure average vendor acceptance time and measure average arrival at customer time.

Order Color Palette =

var _Zone = SELECTEDVALUE('Table'[Zones])

var _OrderTime = SELECTEDVALUE('Table'[ total order time])

Return

IF(

    _Zone="A"&&AVERAGE('Table'[ total order time])<=35, //Zone A and Order Time less than or equal 35

    1,

IF(

    _Zone="B"&&AVERAGE('Table'[ total order time])<=30, //Zone B and Order Time less than or equal 30

    2,

IF(

    _Zone="C"&&AVERAGE('Table'[ total order time])<=40, //Zone C and Order Time less than or equal 40

    3,

    4

)))

2. Choose Icon in conditional formatting.

3. Choose Rules in Format Style, then select the measure you are working on (I used average total order for this example).

Icon layout is for icon position.

- Make sure to change "If Value" into equal then put your measure number. For example: in measure average total order time, I used 1 if Zone A and Average Total Order Time less than 35.

- Make sure to change into Number (dont use Percent).

- Select your preferences icons.

Do this for vendor acceptance time and arrival at customer time.

Hope this will help you.

Thank you.

[majid154a]

Thank you @Irwan , my dear brother, for helping me with the solution. I applied your solution, and it works correctly.

Thank you a hundred times

[Irwan]

hello @majid154a 

glad to be a help.

Thank you.