Need help with a measure or two - Distinct Count based on multiple conditions

[MattConner]

As a precursor , this is in a live connection environment.

Using the below example table titled "Parent Clients" I need a couple of measures, and I am stumped. 

First, and most important, I would like a measure that outputs a distinct count of CLIENTS with YTD REVENUE over 250 in Multiple Markets. The desired output should be "1" representing Dina only, even though Genny has revenue in 2 markets, she only have 250+ in one of them

The second measure would be a measure that gives me the total revenue of all of those clients included in the above distinct count. In this case it would be 630.

Thanks in advance.

[v-eachen-msft · Accepted solution]

Hi @MattConner ,

Considering that you are under an environment using Live Connection, I created two measures.

Measure =
VAR a =
    CALCULATE (
        MAX ( 'Table'[YTD Revenue] ),
        ALLEXCEPT ( 'Table', 'Table'[Client] )
    )
VAR b =
    CALCULATE (
        MIN ( 'Table'[YTD Revenue] ),
        ALLEXCEPT ( 'Table', 'Table'[Client] )
    )
RETURN
    IF ( a >= 250 && b >= 250 && a <> b, 1, 0 )

Measure 2 =
SUMX ( FILTER ( 'Table', [Measure] = 1 ), 'Table'[YTD Revenue] )

I think you want to calculate the count, here is the measure. You can use card visual to show it

MeasureCount = 
CALCULATE(DISTINCTCOUNT('Table'[Client]),FILTER('Table',[Measure]=1))

Here is the result.

[Nathaniel_C]

Hi @MattConner ,

Added a col in PQ,

d

Then summarized that table in PBI

New1 = SUMMARIZE(REV,REV[Client],"HOW MANY MKTS", Count(REV[REV]), "TOTAL REV",SUM(REV[REV]),"How many mkts over 250",Sum(REV[Over 250]))

Clients with 2 mkts or more over 250k = IF(MAX(New1[HOW MANY MKTS])>=2 && max(New1[How many mkts over 250])>=2,1,0)

Still working on the total, but you can see it in the table if you use

m1 = IF([Clients with 2 mkts or more over 250k] = 1, max(New1[TOTAL REV]))

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[MattConner]

@Nathaniel_C  The actual data model is a Live Connection, so (from what I can tell) I cannot use power Querie. 

[Nathaniel_C]

Ok,

will move it to PBI

[Nathaniel_C]

Over 250 = IF((REV[REV])>250,1)

Adds the same col to the table which I called REV, and then the New1 table picks it up with no change.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Nathaniel_C]

Sum REV for Participating Clients = CALCULATE(SUM(New1[TOTAL REV]),Filter(New1,[Clients with 2 mkts or more over 250k] ))

Hi @MattConner ,

Sums the rev for Clients who are eligible.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Nathaniel_C]

Hi @MattConner ,

One more change, I made Genny and came up with a new measure which used COUNT and filtered the table by those who qualified.

Number of clients with 2 and over 250k = CALCULATE(COUNT(New1[Client]),Filter(New1,[Clients with 2 mkts or more over 250k] ))

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel