Conditional Formula and Formatting based on a value in colum

[homboy27]

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

[homboy27 · Accepted 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?

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[v-nuoc-msft]

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]

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?

[v-nuoc-msft]

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

[v-nuoc-msft]

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 · Accepted 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?

[v-nuoc-msft · Accepted solution]

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.

[Jihwan_Kim]

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

[homboy27]

That doesnt work for me. Any other ideas?

[homboy27]

For some reason it doesnt take it.