topic Re: How to get the Total of the Matrix Table in Card Visual in DAX Commands and Tips

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

Sameer21880 — Tue, 15 Mar 2022 13:12:25 GMT

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

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

Whitewater100 — Tue, 15 Mar 2022 13:31:53 GMT

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)

)

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

Jihwan_Kim — Tue, 15 Mar 2022 13:38:56 GMT

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

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

Sameer21880 — Tue, 15 Mar 2022 13:59:41 GMT

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

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

Sameer21880 — Tue, 15 Mar 2022 14:08:53 GMT

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

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

Whitewater100 — Tue, 15 Mar 2022 14:16:01 GMT

CT =

var vcol1 = 'Table'[Col 1]

return

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

FILTER(ALL('Table'),

'Table'[Col 1] = vcol1)

)

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

Sameer21880 — Tue, 15 Mar 2022 14:43:25 GMT

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

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

Whitewater100 — Tue, 15 Mar 2022 14:44:54 GMT

Hi:

Maybe it is as easy as

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

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

Whitewater100 — Tue, 15 Mar 2022 16:39:12 GMT

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.

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

v-zhangti — Fri, 18 Mar 2022 09:13:14 GMT

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.