Column subtotal having distinctcount values

[Anonymous]

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! 

 

[amitchandak]

@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]

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.

[Anonymous]

@amitchandak have attached the SC above

[v-xuding-msft · Accepted solution]

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] )