Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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.
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?
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.
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.
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?
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.
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 |
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.
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?
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.
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" )
That doesnt work for me. Any other ideas?
For some reason it doesnt take it.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
77 | |
74 | |
57 | |
38 | |
33 |
User | Count |
---|---|
70 | |
63 | |
55 | |
48 | |
46 |