Conditional formatting in pivot table

[Ivan1984]

Dear all,

I need some help.
I try to format in pivot table with two different colors, depending on the status column in the database. unfortunately I was unable to find a solution. I would like my client name to be green marked if the status is "confirmed" in the pivot table, if it is reserved in yellow.
The color in the pivot table must be the same as in the base on Client name.

Example is in attachment.

Thanks in advance, I am very grateful.

 

[Chihiro]

How should result look like?
If you need client field to have CF applied. You can't. Unless you add measure to return status. Use that in a Pivot Table field.
Then apply CF via code if you need it to change with pivot table interaction.

There is KPI, but this requires KPI field to be added to Pivot Table.
Quick sample looks like below.

 

[Ivan1984]

Chihiro,

Thank you so much for your time. Unfortunately I don't understand what you mean.

I added the last column to the database where I merged the client and status. I thought I would be able to get what I wanted with it, but I’m doing something wrong.

I wish it looked like this.




If this is not possible with the current column, can I maybe get what I want with the last column in the database. sorry but i can't figure out how to get it.

Chihiro, tnx again for your time and help!

Best regards,
Ivan

 

[Chihiro]

Have a read of...
Key Performance Indicators (KPIs) in Power Pivot - Excel (microsoft.com)

You can add measure like below.
KPI:=SWITCH(FIRSTNONBLANK(Table1[Status],1),"Confirmed",2,"Reserved",1)


Note: You could use CUBE functions to circumvent some of these issues. However, there's not enough benefit when you consider amount of work that has to be done to set it up right.

 

[Ivan1984]

Chihiro,
thank you very much.
I will definitely read the link, thanks again.

I succeeded with slightly changed settings. Now I have a new problem.
I have multiple clients in one position, how can I separate them? "Lidl, Rezervirano" then below with same row labels description "Suzuki, Potvrđeno"
example below:



If you ever come to the capital of Croatia, come in, I'll pay for a drink :-D

 

[Chihiro]

No idea. Since I'm not a mind reader

I'd recommend you upload sample workbook. If I had to take a guess, you used CONCATENATEX? There's no point in using that in your use case.

 

[Ivan1984]

Sorry Chihiro, i was sure that you are Mentalist :-D

Yes, I use CONCATENATEX because i didnt know how to use KPI, I am sure KPI is better option.
Please, help... :-D
If u had some other idea, please, let me know

thank you very much.