Forum Discussion

MattConner's avatar
MattConner
Frequent Visitor
6 years ago
Solved

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

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.

Need Help
Tips and Tricks
Tutorial Requests
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    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.

     

7 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    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's avatar
      MattConner
      Frequent Visitor
      6 years ago

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

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    6 years ago

    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.