Forum Discussion
Power Bi Visual matrix to highlight the given information
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,)
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.
6 Replies
- amitchandak
Super User
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/1682539
https://amitchandak.medium.com/power-bi-where-is-the-conditional-formatting-option-in-new-format-pane-66e0afcb15f3Thanks, Ill try this and see.
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
v-yadongf-msftCommunity Support
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.
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)
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)
