Reply
homboy27
Helper II
Helper II
Partially syndicated - Outbound

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

Syndicated - Outbound

 

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

Syndicated - Outbound

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

Syndicated - Outbound

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/
v-nuoc-msft
Community Support
Community Support

Syndicated - Outbound

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.

Syndicated - Outbound

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?

Syndicated - Outbound

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.

 

Syndicated - Outbound

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

Syndicated - Outbound

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.

Syndicated - Outbound

 

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

 

Syndicated - Outbound

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

Syndicated - Outbound

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Syndicated - Outbound

That doesnt work for me. Any other ideas?

Syndicated - Outbound

For some reason it doesnt take it. 

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

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

Feb2025 NL Carousel

Fabric Community Update - February 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)