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

     

Nathaniel_C's avatar
Nathaniel_C
Icon for Community Champion rankCommunity 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. 

  • Nathaniel_C's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Ok,

    will move it to PBI

    • Nathaniel_C's avatar
      Nathaniel_C
      Icon for Community Champion rankCommunity Champion
      6 years ago
      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's avatar
        Nathaniel_C
        Icon for Community Champion rankCommunity Champion
        6 years ago
        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