cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Memorable Member

## Highlighting the max value (%) in a measure

Hello All,

I have data of suppliers with their regular prices and MSRP. I have created a measure that calculates % difference on regular prices with MSRP as a base value.

Now I want to highlight the row with the highest % difference. Please help me on how can calculate the same.

My data looks like below.

1 ACCEPTED SOLUTION
Community Support

Hi @AbbasG ,

I modified my color measure. Please take a look.

``````Color =
VAR _rank =
RANKX (
FILTER (
ALLSELECTED ( 'All suppliers Combined Data' ),
[Model#] = MAX ( 'All suppliers Combined Data'[Model#] )
),
[Min of MSRP % difference from Min of Price],
,
DESC,
DENSE
)
RETURN
IF ( _rank = 1, "Green", "White" )
``````

Best Regards,

Stephen Tao

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

9 REPLIES 9
Super User

Highest Diff percent

overall = calculate(maxx(summarize(allselected(Table) , Table[Supplier], Table[Model], " _1", [Diff]) , [_1] ) )

Max By supplier = calculate(maxx(summarize(Table , Table[Supplier], " _1", [Diff]) , [_1] ), removefilters(Table[Model] ))

Max By supplier = calculate(maxx(summarize(Table , Table[Model], " _1", [Diff]) , [_1] ), removefilters(Table[Supplier] ))

Use one of them as per need to compare and create color measure

Color =  Switch( True() ,

[Diff %] = [Overall] , "Green" ,"White")

And use that in coditional formatting using field value option

How to do conditional formatting by measure and apply it on pie?: https://youtu.be/RqBb5eBf_I4

Memorable Member

Hello @amitchandak

Thank you for the quick response. I have created the below measures. But it highlights all the value, can you please guide me what goes wrong?
Max By supplier = calculate(maxx(summarize(Table , Table[Supplier], " _1", [Diff]) , [_1] ), removefilters(Table[Model] ))

Color =  Switch( True() ,

[Diff %] = [Overall] , "Green" ,"White")

Super User

@AbbasG , I think I should not have removed model from summarize

Max By supplier = calculate(maxx(summarize(Table , Table[Supplier],Table[Model] " _1", [Diff]) , [_1] ), removefilters(Table[Model] ))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Memorable Member
Memorable Member

Community Support

Hi @AbbasG ,

Is this the desired result you want?

Color measure is as follows.

``````Color =
VAR _rank =
RANKX (
FILTER (
ALLSELECTED ( 'All suppliers Combined Data' ),
[Supplier] = MAX ( 'All suppliers Combined Data'[Supplier] )
),
[Min of MSRP % difference from Min of Price],
,
DESC,
DENSE
)
RETURN
IF ( _rank = 1, "Green", "White" )
``````

Best Regards,

Stephen Tao

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

Memorable Member

Hi @v-stephen-msft thank you for your response.

I want to highlight only the max % diff in each row.

Community Support

Hi @AbbasG ,

I modified my color measure. Please take a look.

``````Color =
VAR _rank =
RANKX (
FILTER (
ALLSELECTED ( 'All suppliers Combined Data' ),
[Model#] = MAX ( 'All suppliers Combined Data'[Model#] )
),
[Min of MSRP % difference from Min of Price],
,
DESC,
DENSE
)
RETURN
IF ( _rank = 1, "Green", "White" )
``````

Best Regards,

Stephen Tao

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

Memorable Member

@v-stephen-msft This helps. Thank you so much 🙂

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors