How to get Max values with respective data and highlight the status.

[saivina2920]

I have the below data.

I want to get max details and it's respective values.

If value matching we should highlight the color.

For Example,
1) First, In the above table find the MAX value of "GRADE" Column.
2) Second Filter only "Excellent" data of all the four columns (DEPT-A,DEPT-B,DEPT-C,DEPT-D) for that MAX Values.
3) Third, If all the values are "Excellent" then it should highlight color as "GREEN" in the Max value text.
If found any one is "Poor" in all the four columns for the max values, it should highlight color as "RED" in the Max value text.

4) Fourth, Show the details of the data when click the "+" symbol, it should show the details list of the final filter output data.

enclosed sample data for reference.

https://1drv.ms/x/s!AiSRcgO5FUmN8VUgHFK3lA7fLLcu?e=XqogPK 

[Icey · Accepted solution]

Hi @saivina2920 ,

Something like this:

Best Regards,

Icey

[Anonymous]

hi 
use the following steps
1. Max Value of grades

max value = CALCULATE(MAX(data[GRADE]),ALL(data))

use a card visual to display this value

2. background color of the max grade value 

color2 =

CALCULATE (

IF (

SELECTEDVALUE ( data[DEPT-A] ) = "EXCELLENT"

&& SELECTEDVALUE ( data[DEPT-B] ) = "EXCELLENT"

&& SELECTEDVALUE ( data[DEPT-C] ) = "EXCELLENT"

&& SELECTEDVALUE ( data[DEPT-D] ) = "EXCELLENT",

"green",

"red"

),

FILTER (

data,

data[GRADE] = [max value]

)

)

3. For the background color of the card visual , format the background color as following

3. Create a table visual with all department columns and grade column . in the filter pane for the visual do as following

to get a table like this 

4. Create 2 bookmarks 1 with  and another without the table visual.

5. add a button to use these bookmarks as actions

if this post helps, Accept it as a solution.

give kudos too!!!

[Icey]

Hi @saivina2920 ,

One sample, please check the attached .pbix file:

Solution1. UnPivot the Dept columns.

Solution2. Don't change the data structure.

I use bookmarks and buttons, filters on this visual, etc..

Best Regards,

Icey

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

[saivina2920]

Excellent..Everything good and working..

one small question here.

Is it possible to maintain the details table (+) in the same page instead of next page.. Because, it is getting confused when it goes to next page....

[Icey]

Hi @saivina2920 ,

When you click on the "+", it will jump to the bookmark page. And if you hide the bookmark page, when the end user views the report, he will not realize that it jumps to another page.

If this can't meet your requirements, you can also refer to this blog to use a card visual to cover the detailed table like so:

Best Regards,

Icey

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

[saivina2920]

Can you give some more idea about my new changing requirement...

[Icey]

Hi @saivina2920 ,

I'm working on it currently and will get back to you as soon as I have any progress. Please be patient. Thank you for your understanding and support.

Best Regards,

Icey

[saivina2920]

Thanks for your extradinary help. The requirement is little bit changed. 

I tried to derive and match the requirement. But, data not coming properly.

Enclosed PBIX file here. : https://1drv.ms/u/s!AiSRcgO5FUmN8Vu-piJFTP9Q1yQ0?e=yMlwvG 

Complete requirement

1) take the maximum value of all 4 columns (column contains GRADE)
2) Exclude the column if anyone value is "NO"
3) Show the maximum value in the card.
Highlight the color as mentioned below condition.
If green ==> Check maximum value's available in all the 4 columns. No Need to display the "+" expand symbols or details card.
If Red ==> Check the maximum values's in all the 4 columns, if the max value is not available in any one column, then show the
max value of every column max value in table.
Here Need to display the "+" expand symbols or details card.

Note : The data has to be populated based on the CATEGORY. (Default Category should display while loading the page "GROUP-A")

[Icey]

Hi @saivina2920 ,

Please check if this could meet your requirements. I use "Drill through" instead of bookmark here.

Here are some details to be aware of.

If there is anything you don't understand, please let me know and I will explain it to you in detail.

Best Regards,

Icey

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

[saivina2920]

Hi @Icey,

can you pls. update for the changing requirement for the same...?

[Icey]

Hi @saivina2920 ,

Let me have a test.

Best Regards,

Icey

[saivina2920]

Hi @Icey 

Can you pls. update which i sent the last queries.? 

pls. update when you are free...

[saivina2920]

You are the best. 

Some condition is missing here...

1. We cannot exclude direct coulmn which  contains "NO" . the current data is "DEPT-C_GRADE" has contains "NO" values. 

"NO" values shall come at any of the columns. So it is dynamic. 

"NO" Value should search all the four columns. If found "NO" values of anyone of the columns then it must exclude.

2. Category values should come in dropdown (like value GROUP-A,GROUP-B)

If the above is derived, the requirement is met and problem solved.

pl. update the logic and give us the final output. let me know if you need any more details.

[Icey]

Hi @saivina2920 ,

For the first thing, please check "DATA (4)" table in the attached .pbix file.

For the second, if use category values in a slicer which can dropdown, drill-through, which is dynamic, will not work. And bookmark is static.

Best Regards,

Icey

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

[saivina2920]

First thing seems good..

Second one is by default category should be highlighted as "GROUP-A" and let user select any one option, not both at same time. I think, this is may be possible.

Because category conatains more than ten values and let end user decide which one they want. no need to select more than two option that category..

Question : Will it possible to select only one otpion in category dropdown at any time...?

Finally give only two sheets which contains like sheet3 and sheet3-Bookmark with all added sheet-4 queries...

All sheet i am getting confused.

If Red :  symbol "+" should enable and we want to show the details in the next sheet like sheet-3 bookmark

If Green : Hide "+" symbol, no need to show anything.

[Icey]

Hi @saivina2920 ,

You can select only one option in slicer by turning on "Single select" option.

However, drill-through button doesn't work when using a slicer. If you want to keep slicer, it is suggested to keep both slicer and treemap visual. And before clicking on the drill-through button, it is needed to click on the treemap firstly. You can test it.

Best Regards,

Icey

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

[saivina2920]

Hi @Icey,

Can you pls. update my last query..?

pls.check my pbix file for the Max value Visual card background color..?

This is important for this visual...

[Icey · Accepted solution]

Hi @saivina2920 ,

Best Regards,

Icey

[saivina2920]

Hi @Icey ,

I categorized data and measures in seperately with new pbix file. 

Everything is coming perfect. but, i am not getting the background color in the Max value Visual card.

In the measured a already given the color.

Anything i missed...

Can you pls. give your guidance where is the problem...?

New Pbix file ==> https://1drv.ms/u/s!AiSRcgO5FUmN8WjBv3ZSGzjuMQhF?e=eoBUiu 

can you pls. give some guidance..?

[saivina2920]

can you pls. update the final PBIX file for all the 4th sheet logic.

we need details in seperate section for easy understanding...

[Icey · Accepted solution]

Hi @saivina2920 ,

Something like this:

Best Regards,

Icey