cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
prmpn Frequent Visitor
Frequent Visitor

Total Row Count in Matrix Visual with Visual level filter

Dear Community,

I have a scenario where i have department with multiple codes. Each department has 3 sub departments with each sub departments having codes on different months. I am using matrix visual to show the count of Codes in each Department per month. I could get that in matrix. But when using the sub department in visual level filter, the total also gets filtered. I need to the get the overall / original total for each sub department even if the visual level filter is applied. I have tried using All, AllSelected & All except in measures.

 

Please help me to get the overall total in using measures as my source is live connection.

Attached the image for reference & find the link below for the sample PBIX file i have used to the screen shot.

 

PBIX download Link : https://filebin.net/uzhobbcdsec2zrh2

(has Both PBIX & the Sample Excel)

Thanks In Advance.

 

Note: Below Expected result is what i need to get after filtering Sub Depart into Visual Level Fitler but with Overall totalPBIX & SourceMatrix sample.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Total Row Count in Matrix Visual with Visual level filter

@prmpn hey add following two measures and then use Code Count in your matrix

 

Total Count = COUNT( Child[Code] )

Code Count = 
IF (
    ISINSCOPE( Child[Depart]), 
    [Total Count], 
    CALCULATE( 
        [Total Count],
        ALL(Child[Depart])
    )
) * DIVIDE( [Total Count],[Total Count])





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





12 REPLIES 12
Super User
Super User

Re: Total Row Count in Matrix Visual with Visual level filter

@prmpn hey add following two measures and then use Code Count in your matrix

 

Total Count = COUNT( Child[Code] )

Code Count = 
IF (
    ISINSCOPE( Child[Depart]), 
    [Total Count], 
    CALCULATE( 
        [Total Count],
        ALL(Child[Depart])
    )
) * DIVIDE( [Total Count],[Total Count])





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





prmpn Frequent Visitor
Frequent Visitor

Re: Total Row Count in Matrix Visual with Visual level filter

Hi @parry2k ,

 

Thank you for the solution. 

 

It works perfectly in ths sample code i have provided. But my actual scneraio has a cube source and ISINScope is not available for Live connection sources. Is it possible to get a solution for a live connection sources please. 

 

Kindly let me know about this. 

 

Thanks & Regards,

Praveen

Super User
Super User

Re: Total Row Count in Matrix Visual with Visual level filter

Oh man, ISINSCOPE is amazing.  It would have solved so many problems I've had with matrices.  Thank you for pointing it out!

tex628 New Contributor
New Contributor

Re: Total Row Count in Matrix Visual with Visual level filter

Can you do a

IF(
Selectedvalue('Departments'[Department]) = BLANK()
, 
Calculate([Total count] , all('Departments')
,
[Total count])

Department being the column dimension that your using. 

Super User
Super User

Re: Total Row Count in Matrix Visual with Visual level filter

@tex628 
The problem with that solution is that you still have a SELECTEDVALUE if there's only one record in the subgroup. So it would work in most cases, but still give you the wrong result when there's only one value.

Honestly, these workarounds for matrices are ridiculous, and I encourage everybody to vote on this idea to allow custom totals/subtotals: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/17401381-conditional-formatting-f...

prmpn Frequent Visitor
Frequent Visitor

Re: Total Row Count in Matrix Visual with Visual level filter

Hi @tex628 ,

Thanks for your reply. PBI is not allowing  to use a calcualte column inside a IF condition. Please find the image for reference. Glad you could try for an solution. Kindly do let me know if there are any workaround for the same. 

 

Thanks  &Regards,error image.pngPraveen 

 

tex628 New Contributor
New Contributor

Re: Total Row Count in Matrix Visual with Visual level filter

Heheh you are totally correct! 

I had a similar issue a while back, which i solved by adding blank rows to the table. 
Essentially what i did was:

A1A1
A1BLANK()


By doing this to every record and then using one column for the slicer and one for the matrix i could add a blank row to every record and through that always use selectvalue to identify the total. 

Im not sure if it can be applied to your model but its worth a try maybe Smiley Happy

tex628 New Contributor
New Contributor

Re: Total Row Count in Matrix Visual with Visual level filter

The example i gave will not give you the result that you are looking for, sorry! Take a look at my previous reply and see if that can be applied. 

In regards to the image below, I forgot to add a ")" after the all('Parent') statement, thats why it's not working!

prmpn Frequent Visitor
Frequent Visitor

Re: Total Row Count in Matrix Visual with Visual level filter

Hi @tex628 ,

 

I tried including the close bracket ")" and im getting the row level fitlered count only. As my source is Live connection, im not able to alter the data. Thanks for your suggestion and help. Will try out an solution for this. 

 

Thanks alot. 

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 110 members 1,556 guests
Please welcome our newest community members: