Power Bi Visual matrix to highlight the given information

[Hassa0213]

Hi please help with the below to create a power bi usual. Big help. I have the table data as below.

Company	Inovation Stage	Ticket
Company A	1.stage	101
Company B	1.stage	102
Company A	2.stage	103
Company A	4.stage	104
Company B	3.stage	105
Company B	4.stage	106

and I'm trying to have the below visual.

Company Name	Highest Stage achived	Ticket
Company A	stage 4
1.stage		101
2.stage		103
4.stage		104
Company B	stage 4
1.stage		102
3.stage		105
4.stage		106

For each company I want to highligh the highest Innovation stage achived(as in col 2). Im not sure how to make a measure to do this. I added this information to power bi matrix but still having trouble getting the highest innovation stage achived and matrix aggregate the tickets. If I can get a visual similar to the above its fine. please advice.

What I did with matrix is below but the Ticket aggregation is wrong.(308,313,)

[v-yadongf-msft · Accepted solution]

Hi @Hassa0213 ,

I think this is pretty close to what you want:

Maybe you can think about it.

Create a new column:

Highest Stage achived = IF( 'Table'[Inovation Stage] = MINX(FILTER('Table','Table'[Company] = EARLIER('Table'[Company])),'Table'[Inovation Stage]), MAXX(FILTER('Table','Table'[Company] = EARLIER('Table'[Company])),'Table'[Inovation Stage]))

Then create a matrix:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Hassa0213]

Hi Thanks for the answer, I tried your answer and got the below visual.

but the Highest Innovation Stage here is highlighted for the ID's 101 and 102. The requirment I have is to show the highest Innovation stage when the Innovation stage column is drill up to the company level as shown below.

I have more drill down data now. I used the measure as below and got the Highest Innovation Stage.

Innov_m = var stage_s = CALCULATE(MAX(Company_table[Inovation Stage]), ALLEXCEPT(Company_table,Company_table[Inovation Stage], Company_table[Company Name]))

return CALCULATE(MAX(Company_table[Inovation Stage]),FILTER(Company_table, (Company_table[Inovation Stage])= stage_s))

But when I drill upto the region level I dont want to see the Highest Innovation stage but it is showing as below. (Please ignore the sum of ID column)

[Hassa0213]

Hi Thanks for the answer, I tried your answer and got the below visual.

but the Highest Innovation Stage here is highlighted for the ID's 101 and 102. The requirment I have is to show the highest Innovation stage when the Innovation stage column is drill up to the company level as shown below.

I have more drill down data now. I used the measure as below and got the Highest Innovation Stage.

Innov_m = var stage_s = CALCULATE(MAX(Company_table[Inovation Stage]), ALLEXCEPT(Company_table,Company_table[Inovation Stage], Company_table[Company Name]))

return CALCULATE(MAX(Company_table[Inovation Stage]),FILTER(Company_table, (Company_table[Inovation Stage])= stage_s))

But when I drill upto the region level I dont want to see the Highest Innovation stage but it is showing as below. (Please ignore the sum of ID column)

[amitchandak]

@Hassa0213 , Not very clear

if you want to show max at the company level

maxx(Values(Table[Stage]), calculate(Sum(Table[Ticket])) )

If you want find max at company level and highlight

max 1 = calculate(maxx(Values(Table[Stage]), calculate(Sum(Table[Ticket])) ), allselected(Table[Company]))

Then you can have color measure and use that in conditional formatting using field value option

If([Max 1] = Sum(Table[ticket]) , "Green", "White")

How to do conditional formatting by measure and apply it on pie?
https://www.youtube.com/watch?v=RqBb5eBf_I4&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L
https://community.powerbi.com/t5/Community-Blog/Power-BI-Conditional-formatting-the-Pie-Visual/ba-p/...
https://amitchandak.medium.com/power-bi-where-is-the-conditional-formatting-option-in-new-format-pan...

[Hassa0213]

Hi Sorry the question was not clear. There is 3 columns company name, innovation stage and ticket ID. The user will submit the ticket ID for each company for each innovation stage.  The innovation stages for each company is ‘1.Stage’ , ‘2.Stage’, ‘3.Stage’, ‘4. Stage’ .  I want to show the user submitted current highest (max) innovation stage at the company level.

And also show the ticket ID without summing it in another column.

Please let me know how to get a similar visual to this in a matrix or in any table view? Thanks

I want an output table information as shown below. Thanks a lot.

Company Name	Highest Stage achived	Ticket ID
Company A	3.stage
1.stage		101
2.stage		103
3.stage		104
Company B	4.stage
1.stage		102
2.stage		105
3.stage		106
4.stage		107

[Hassa0213]

Thanks, Ill try this and see.