Forum Discussion
Helper IIIConditional Formula and Formatting based on a value in colum
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.
homboy27Thank 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.
- homboy27
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