Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Sameer21880
New Member

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

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 1Col 2Col 3
A473Status1
A473Status1
A473Status1
A473Status2
A473Status2
B473Status2
B637Status2
B637Status2
C816Status2
C816Status3
C816Status3
C816Status3
C816Status3
C816Status3

 

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.

A1
B2
C1
Grand Total3

 

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

 

Thanks

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

vzhangti_1-1647594675390.png

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.

View solution in original post

9 REPLIES 9
Jihwan_Kim
Super User
Super User

Hi,

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

 

Picture1.png

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

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 1Col 2Col 3
A473Status1
A473Status1
A473Status1
A473Status2
A473Status2
B473Status2
B637Status2
B637Status2
C816Status2
C816Status3
C816Status3
C816Status3
C816Status3
C816Status3

 

Sameer21880_0-1647352551887.png

 

Sameer21880_1-1647352581638.png

 

Thanks

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

vzhangti_1-1647594675390.png

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
Solution Sage
Solution Sage

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

CT =
var vcol1 = 'Table'[Col 1]
return
CALCULATE(DISTINCTCOUNT('Table'[Col 2]),
FILTER(ALL('Table'),
'Table'[Col 1] = vcol1)
)
 
Whitewater100_1-1647353750226.png

 

Whitewater100_0-1647353684212.png

 

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

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 1Col 2Col 3
A473Status1
A473Status1
A473Status1
A473Status2
A473Status2
B473Status2
B637Status2
B637Status2
C816Status2
C816Status3
C816Status3
C816Status3
C816Status3
C816Status3

 

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:

Sameer21880_2-1647353198794.png

 

Sameer21880_3-1647353199278.png

 

Thanks

 

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.

Hi:

Maybe it is as easy as

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.