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.
Helper IIIThank 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 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.
- homboy27
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?