Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Column subtotal having distinctcount values

Hey guys, 

 

Immediate request, 

 

I have a matrix table, I'm using distinct count for my column(Hours column). I would need to sum column subtotal (5, 7,9,7,8) =36

 

Pls help! 

 

Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi Anonymous ,

     

    Please reference this to have a try.

    (Replace "Product" with your "Hour" column)

     

    Distinct count =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Product] ),
    ALLSELECTED ( 'Table'[Date].[Month] )
)

    Total values =
VAR t =
    SUMMARIZE (
        'Table',
        'Table'[Date].[Month],
        'Table'[Product],
        "distinct count", [Distinct count]
    )
RETURN
    SUMX ( t, [Distinct count] )

     

     

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , you should give a better screenshot and date

     

    You have try like

    sumx(summarize(Table, Table[row],table[col],"_1",distinctCOUNT(Table[Col1])),[_1])

     

    Here Table[row],table[col] are your group by , you have given in matrix row and column, They can be one or many as per need

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      apologies for the screenshot.

       

      i did use your measure but seems like it not solving my purpose.

      attaching the SC, left is with the help of measure you suggessted, right one is original. I need unique hour counts but it seems like with the measure it counting the hours.