Forum Discussion

homboy27's avatar
homboy27
Icon for Helper III rankHelper III
1 year ago
Solved

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 man...
Need Help
Show and Tell
Tips and Tricks
  • homboy27's avatar
    homboy27
    1 year ago

     

    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?

     

     

  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
homboy27
Icon for Helper III rankHelper III
1 year ago

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
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      homboy27
      Icon for Helper III rankHelper III
      1 year ago

       

      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?

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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.