Count at first dimention for straight table

[#1]

Dear Friends

I have a sample data given below;

id	Sub Category	Spend Data
15	Galley/Laundry	In Progress
17	Galley/Laundry	In Progress
60	Galley/Laundry	Completed
61	Galley/Laundry	Completed
67	Galley/Laundry	Not Applicable
77	Galley/Laundry	Not Applicable
80	Galley/Laundry	Not Applicable
82	Galley/Laundry	In Progress
83	Galley/Laundry	Completed
10	Trading house	Completed
12	Trading house	Completed
13	Trading house	Not Applicable
45	Trading house	In Progress
63	Trading house	Not Applicable

for each id there can be 4 different stages

Not Started - Red

In Progress - Yellow

Completed - Green

Not Applicable - Blue

None of these - Cross

Enclosed qvd provide this output at id level.

However, I want to create a similar picture at sub category level.

No of project id

1. Not Started
2. In Progress
3. Completed

Count the maximum no of id of above three & display the relavant color for the subcategory.

if there is a tie, then color in order Green, yellow, red

it should ignore the not applicable cases at subcategory level.

Request some suggestions!

[#2]

Dear Friends

Any suggestion on above request is highly appreciated.

Best Regards

Rahul

[Gysbert_Wassenaar · Accepted solution]

See attached example. If you want images instead of count on a background color, simply replace the expression with the background color expression and replace the color functions with the reference to the images you want.

[#4]

Thanks Gysbert!