Forum Discussion

dw700d's avatar
dw700d
Post Patron
3 years ago
Solved

distinct count aggregate

Good day all,

 

I am trying to create a measure that calculates the distinct count of Circuits with a profit greater than 0.   I am using the measure below. The measure appears to be evaluating on a row by row basis. as a result it is telling me that there are 2 Circuits with a profit greater than 0.  but I want the measure to evaluate the total profit for the circuit . Based on my data below the measure should return "1" because Circuit A has a -1,115 profit and Circuit C has a 1,789

 

Count of Profit Circuit =

CALCULATE(DISTINCTCOUNT('job'[Circuit #]),FILTER('job','job'[ Profit]>0))

 

 

Circuit #Date.Revenue Expense Profit  
A5/1/2022 0:00          108299-191
A6/1/2022 0:00108299-191
A10/1/2022 0:00115299-184
A11/1/2022 0:00115299-184
A12/1/2022 0:00115299-184
A7/1/2022 0:00116299-183
A8/1/2022 0:00116299-183
A9/1/2022 0:00119299-180
A2/1/2022 0:00109249-140
A3/1/2022 0:00109249-140
A1/1/2022 0:00437249188
A4/1/2022 0:00756299457
C4/1/2022 0:0080075842
C5/1/2022 0:0082075862
C6/1/2022 0:0083075872
C7/1/2022 0:00900758142
C8/1/2022 0:0080075842
C9/1/2022 0:0080075842
C2/1/2022 0:00830632198
C3/1/2022 0:00800632168
C10/1/2022 0:00900379521
C1/1/2022 0:00300100200
C11/1/2022 0:002000200
C12/1/2022 0:001000100

 

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    dw700d Try:

    Count of Profit Circuit version 2 =
COUNTROWS(
    FILTER(
        SUMMARIZE(
            'Inventory',
            [Circuit #],
            "_Profit", SUM(Inventory[Profit])
        ),
        [_Profit] > 0
    )
)

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    dw700d Try:

    Measure =
  COUNTROWS(
    FILTER(
      SUMMARIZE(
        'Table',
        [Circuit],
        "__Profit", SUM('Table'[Profit])
      )
      [__Profit] > 0
    )
  )
    • dw700d's avatar
      dw700d
      Post Patron
      3 years ago

      Greg_Deckler   thank you for your help. I am not quite sure what I am doing wrong. Any thoughts

       



       
      Count of Profit Circuit version 2 =
      COUNTROWS(
          FILTER(
              SUMMARIZE(
                  'Inventory',
                  [Circuit #],
                  "_Profit", SUM(Inventory[Profit])
              )[_Profit], SUM(Inventory[Profit])
          )
          [_Profit] > 0
      )
      )
      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        3 years ago

        dw700d Try:

        Count of Profit Circuit version 2 =
COUNTROWS(
    FILTER(
        SUMMARIZE(
            'Inventory',
            [Circuit #],
            "_Profit", SUM(Inventory[Profit])
        ),
        [_Profit] > 0
    )
)