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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
homboy27
Helper III
Helper III

Conditional Formula and Formatting based on a value in colum

I have the below column. I would like to highlight any client that has a senior manager and manager level. So for example I would like to highlight client A and C since both of them have a senior manager and a manager level. How would I go about doing that? Would it be switch formula or something? Can you please help me with DAX formula?

 

ClientLevelSales
Client ASenior Manager4000
Client AManager4000
Client ASenior4000
Client BSenior Manager4000
Client BSenior4000
Client CSenior Manager4000
Client CManager4000
Client CSenior4000
Client DManager4000
Client DSenior4000
2 ACCEPTED SOLUTIONS

 

Thank you, please see below. If I click on East, total sales for East is $5k (Left pic) and there are 2 employees so the average for east would be 2.5k, not 1k. Do you know how to update for that?

 

homboy27_0-1739394168503.png

 

View solution in original post

Anonymous
Not applicable

Hi @homboy27 

 

AvgSalesPerEmployeeRegion = 
AVERAGEX(
    SUMMARIZE(
        'Table',
        'Table'[Region],
        'Table'[Employee Name],
        "AvgSales", SUM('Table'[Sales])
    ),
    [AvgSales]
)

 

vnuocmsft_0-1739433772831.png

 

Regards,

Nono Chen

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

11 REPLIES 11
Ashish_Mathur
Super User
Super User

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1739159526309.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi @homboy27 

 

Thank you very much Jihwan_Kim for your prompt reply.

 

For your question, here is the method I provided:

 

Here's some dummy data

 

“Table”

vnuocmsft_0-1738918003809.png

 

Create a measure.

 

HasSeniorManagerAndManager = 
VAR SeniorManagerCount = 
CALCULATE(
    COUNTROWS('Table'), 
    'Table'[Level] = "Senior Manager",
    ALLEXCEPT('Table', 'Table'[Client])
)
VAR ManagerCount = 
CALCULATE(
    COUNTROWS('Table'), 
    'Table'[Level] = "Manager", 
    ALLEXCEPT('Table', 'Table'[Client])
)
RETURN 
IF(
    SeniorManagerCount > 0 && ManagerCount > 0, 
    "Yes", 
    "No"
)

 

Create a table to display the [Client], and filter the data where [HasSeniorManagerAndManager] is "Yes".

 

vnuocmsft_1-1738918255615.png

 

Here is the result.

 

vnuocmsft_2-1738918282713.png

 

If you're still having problems, provide your desired outcome. It is best presented in the form of a table.

 

Regards,

Nono Chen

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

Thank you that worked, had another question, how would I filter out anyone that has sales under 3,000 and then still apply the conditional formatting?

Anonymous
Not applicable

Hi @homboy27 

 

You just need to add a judgment condition to the appeal code:

 

'Table'[Sales] > 3000,

 

For example:

 

Over 3000 HasSeniorManagerAndManager = 
VAR SeniorManagerCount = 
CALCULATE(
    COUNTROWS('Table'), 
    'Table'[Level] = "Senior Manager",
    'Table'[Sales] > 3000,
    ALLEXCEPT('Table', 'Table'[Client])
)
VAR ManagerCount = 
CALCULATE(
    COUNTROWS('Table'), 
    'Table'[Level] = "Manager",
    'Table'[Sales] > 3000,
    ALLEXCEPT('Table', 'Table'[Client])
)
RETURN 
IF(
    SeniorManagerCount > 0 && ManagerCount > 0, 
    "Yes", 
    "No"
)

 

Here is the result.

 

vnuocmsft_0-1739153267746.png

 

Regards,

Nono Chen

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

 

Thank You - That worked. You are awesome. Also had another question. If i wanted to pull in metrics such as average by employee by region or average by period by employee based on the below data, how would i go about doing that?

 

ClientEmployee NameSalesQuarterRegion
Client AEmployee A40001East
Client AEmployee A40002East
Client AEmployee A40003East
Client BEmployee B40001Central
Client BEmployee B40002Central
Client CEmployee C40003West
Client CEmployee C40004West
Client CEmployee C40001West
Client DEmployee D40002East
Client DEmployee D40001East
Anonymous
Not applicable

Hi @homboy27 

 

Try this:

 

Average Sales by Employee by Region = 
CALCULATE(
    AVERAGE('Table'[Sales]),
    ALLEXCEPT('Table', 'Table'[Employee Name], 'Table'[Region])
)

 

Average Sales by Period by Employee = 
CALCULATE(
    AVERAGE('Table'[Sales]),
    ALLEXCEPT('Table', 'Table'[Quarter], 'Table'[Employee Name])
)

 

vnuocmsft_0-1739239795416.png

 

Regards,

Nono Chen

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

 

Thank you, please see below. If I click on East, total sales for East is $5k (Left pic) and there are 2 employees so the average for east would be 2.5k, not 1k. Do you know how to update for that?

 

homboy27_0-1739394168503.png

 

Anonymous
Not applicable

Hi @homboy27 

 

AvgSalesPerEmployeeRegion = 
AVERAGEX(
    SUMMARIZE(
        'Table',
        'Table'[Region],
        'Table'[Employee Name],
        "AvgSales", SUM('Table'[Sales])
    ),
    [AvgSales]
)

 

vnuocmsft_0-1739433772831.png

 

Regards,

Nono Chen

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

Jihwan_Kim
Super User
Super User

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_1-1738790939454.png

 

 

Jihwan_Kim_2-1738791020693.png

 

 

 

color condition: =
VAR _t =
    FILTER (
        CALCULATETABLE (
            SUMMARIZE ( Data, 'Level'[Level] ),
            ALL ( 'Level'[Level], 'Level'[sort_order] )
        ),
        'Level'[Level] IN { "Senior Manager", "Manager" }
    )
RETURN
    IF ( COUNTROWS ( _t ) >= 2, "yellow" )

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

That doesnt work for me. Any other ideas?

For some reason it doesnt take it. 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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