How to get the Total of the Matrix Table in Card Visual

[Sameer21880]

Hello Everyone,

Please can anyone help me for one of the scenario which I am trying to solve but not working. Below is my requirement.

Raw Data for reference:

Col 1	Col 2	Col 3
A	473	Status1
A	473	Status1
A	473	Status1
A	473	Status2
A	473	Status2
B	473	Status2
B	637	Status2
B	637	Status2
C	816	Status2
C	816	Status3
C	816	Status3
C	816	Status3
C	816	Status3
C	816	Status3

Now, if I want to check for Status2 basis Col1 and distinctcount on Col2 then i will get the below Grand Total=3. I want to show this GRAND TOTAL in my CARD visual.

A	1
B	2
C	1
Grand Total	3

Please help me at the earliest I will be very grateful to you.

Thanks

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

total measure: =
CALCULATE ( DISTINCTCOUNT ( Data[Col 2] ), Data[Col 3] = "Status2" )

[Sameer21880]

Hi Kim,

Thanks for your reply the solution is correct when we consider only 2 Cols but in my scenario I want the distinct count of Status2 basis Col1 and Col2. Means if I filter Status2 in Col3 then Col1 should also be count as unique and corresponding to Col1 values Col2 should also be count as unique and whatever the total that should be displayed on Card.

I have pasted the Pivot Table image for your reference, I just want the grand total in my card. Purpose to show you through Pivot Table for your understanding only. I hope now I am clear with my requirement.

Raw Data for reference:

Col 1	Col 2	Col 3
A	473	Status1
A	473	Status1
A	473	Status1
A	473	Status2
A	473	Status2
B	473	Status2
B	637	Status2
B	637	Status2
C	816	Status2
C	816	Status3
C	816	Status3
C	816	Status3
C	816	Status3
C	816	Status3

Thanks

[v-zhangti · Accepted solution]

Hi @Sameer21880 ,

Please check the methods below.

GRAND TOTAL =
IF (
    HASONEVALUE ( 'Table'[Col 1] ),
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Col 2] ),
        'Table'[Col 3] = SELECTEDVALUE ( 'Table'[Col 3] )
    ),
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Col 2] ), 'Table'[Col 3] = "Status2" )
)

Is this the result you want?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Whitewater100]

Hello:

You can take a calculated column in your table and then pull the result:

CT =

var vcol1 = 'Table'[Col 1]

var vcol2 = 'Table'[Col 2]

var vcol3 = 'Table'[Col 3]

return

CALCULATE(DISTINCTCOUNT('Table'[Col 2]),

FILTER(ALL('Table'),

'Table'[Col 1] = vcol1)

)

[Whitewater100]

CT =

var vcol1 = 'Table'[Col 1]

return

CALCULATE(DISTINCTCOUNT('Table'[Col 2]),

FILTER(ALL('Table'),

'Table'[Col 1] = vcol1)

)

[Sameer21880]

If I am using this measure I am getting Total Count:2 but if you see my pivot table image there is Grand Total:3. I would require DistinctCount of 3. Please help me to get this resolved..

[Sameer21880]

Thanks for your quick response. Actually in my raw data there is no calculated columns. All I just want the distinct count of Col2 values basis Col1 but Col1 should also be unique and then final filter should be Col3 i.e. Status2. Let me show you with the help of Pivot Table below:

Raw Data for reference:

Col 1	Col 2	Col 3
A	473	Status1
A	473	Status1
A	473	Status1
A	473	Status2
A	473	Status2
B	473	Status2
B	637	Status2
B	637	Status2
C	816	Status2
C	816	Status3
C	816	Status3
C	816	Status3
C	816	Status3
C	816	Status3

Pivot Table For Reference to get an idea of my exact requirement: I just want the Grand Total: 3 in my Card Visual but my calculation should be exactly how Pivot Table is showing below:

Thanks

[Whitewater100]

Hi:

Try this as it only gives 3 for total as you asked for.

COUNT = DISTINCTCOUNT(Table[Col2])  That will give 3 as answer, and give correct indivdual answers when in a mtrix or table.

[Whitewater100]

Hi:

Maybe it is as easy as

DISTINCTCOUNT(Table[Col2])  That will give 3 as answer.