Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Gurpreetsingh55
Frequent Visitor

I want to calculate maximum occurring color in a metrics in power BI

SS.pngI have created a matrics in which i have 9 measures and each one has different color coding , i have also calculated their color coding by creating a measure, Now i want to calculate maximum occuring color in that matrics .

colors are Red ,Amber, Green.

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @Gurpreetsingh55 ,

I created some data:

vyangliumsft_0-1709018239490.png

If you conditionally set the color by a combination of measure and Field value, you can use the following.

vyangliumsft_1-1709018252687.png

vyangliumsft_2-1709018252690.png

Here are the steps you can follow:

1. Enter data – create a table.

vyangliumsft_3-1709018266167.png

2. Create measure.

Count_color =
var _measure1=
COUNTX(
    FILTER(ALL('Table'),
    [Jan-color]=MAX('color_Table'[Color])),[Index])
var _measure2=
COUNTX(
   FILTER(ALL('Table'),
    [Feb-color]=MAX('color_Table'[Color])),[Index])
return
_measure1+_measure2
Max_Color =
var _max=
MAXX(
    ALL('color_Table'),[Count_color])
return
MAXX(
    FILTER(ALL('color_Table'),[Count_color]=_max),[Color])

3. Result:

 

vyangliumsft_4-1709018266168.png

 

Best Regards,

Liu Yang

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

View solution in original post

4 REPLIES 4
v-yangliu-msft
Community Support
Community Support

Hi  @Gurpreetsingh55 ,

I created some data:

vyangliumsft_0-1709018239490.png

If you conditionally set the color by a combination of measure and Field value, you can use the following.

vyangliumsft_1-1709018252687.png

vyangliumsft_2-1709018252690.png

Here are the steps you can follow:

1. Enter data – create a table.

vyangliumsft_3-1709018266167.png

2. Create measure.

Count_color =
var _measure1=
COUNTX(
    FILTER(ALL('Table'),
    [Jan-color]=MAX('color_Table'[Color])),[Index])
var _measure2=
COUNTX(
   FILTER(ALL('Table'),
    [Feb-color]=MAX('color_Table'[Color])),[Index])
return
_measure1+_measure2
Max_Color =
var _max=
MAXX(
    ALL('color_Table'),[Count_color])
return
MAXX(
    FILTER(ALL('color_Table'),[Count_color]=_max),[Color])

3. Result:

 

vyangliumsft_4-1709018266168.png

 

Best Regards,

Liu Yang

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

Hi @v-yangliu-msft it is working for me thank you so much for your help.

PijushRoy
Super User
Super User

Hi @Gurpreetsingh55 

Please create a pbix that contains sample data but still reflects your semantic model (tables, relationships, calculated columns, and measures). Upload the pbix to OneDrive, Google Drive, or dropbox and share the link in this thread. If you are using a spreadsheet to create/import the sample data instead of the manual import method, share the spreadsheet as well. 


Hi @PijushRoy , Sorry i cannot share the PBIX and data i just want to know the logic if it can be done by any means.

Thanks

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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