- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?
Client | Level | Sales |
Client A | Senior Manager | 4000 |
Client A | Manager | 4000 |
Client A | Senior | 4000 |
Client B | Senior Manager | 4000 |
Client B | Senior | 4000 |
Client C | Senior Manager | 4000 |
Client C | Manager | 4000 |
Client C | Senior | 4000 |
Client D | Manager | 4000 |
Client D | Senior | 4000 |
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi @homboy27
AvgSalesPerEmployeeRegion =
AVERAGEX(
SUMMARIZE(
'Table',
'Table'[Region],
'Table'[Employee Name],
"AvgSales", SUM('Table'[Sales])
),
[AvgSales]
)
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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”
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".
Here is the result.
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?
Client | Employee Name | Sales | Quarter | Region |
Client A | Employee A | 4000 | 1 | East |
Client A | Employee A | 4000 | 2 | East |
Client A | Employee A | 4000 | 3 | East |
Client B | Employee B | 4000 | 1 | Central |
Client B | Employee B | 4000 | 2 | Central |
Client C | Employee C | 4000 | 3 | West |
Client C | Employee C | 4000 | 4 | West |
Client C | Employee C | 4000 | 1 | West |
Client D | Employee D | 4000 | 2 | East |
Client D | Employee D | 4000 | 1 | East |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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])
)
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi @homboy27
AvgSalesPerEmployeeRegion =
AVERAGEX(
SUMMARIZE(
'Table',
'Table'[Region],
'Table'[Employee Name],
"AvgSales", SUM('Table'[Sales])
),
[AvgSales]
)
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

That doesnt work for me. Any other ideas?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

For some reason it doesnt take it.

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
01-06-2024 08:38 AM | |||
10-17-2023 03:49 AM | |||
02-15-2024 01:28 AM | |||
09-13-2024 05:19 AM | |||
09-06-2024 10:39 AM |