Forum Discussion

homboy27's avatar
homboy27
Helper 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 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?

 

ClientLevelSales
Client ASenior Manager4000
Client AManager4000
Client ASenior4000
Client BSenior Manager4000
Client BSenior4000
Client CSenior Manager4000
Client CManager4000
Client CSenior4000
Client DManager4000
Client DSenior4000
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.

11 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    1 year ago

    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" )

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

    • homboy27's avatar
      homboy27
      Helper III
      1 year ago

      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?

      • 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.