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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
parry2k
Super User
Super User

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


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

12 REPLIES 12
Anonymous
Not applicable

Dear members, 

This accepted solution works best for the scenario i have mentioned in the screenshot(which is import mode).

I solved my acutal issue where my source is Live Connection ( SSAS Cube) and the "ISINSCope" function is not supported yet. 

 

To get the current context i used "MAXA" instead of "ISINSCOPE" to calculate the count. Posting this solution for live connection for any members reference. Thanks all. @parry2k , @Cmcmahan @tex628 

  IF(  
       MAXA(Parent[Depart]), Total Count]
       ,CALCULATE( [Total Count], ALL(Child[Depart])
     )
Cmcmahan
Resident Rockstar
Resident Rockstar

So I went in to play with your .pbix file, and figured out a way to do this without getting crazy and checking context to calculate differently based on whether you're in a parent or child row of the matrix.

 

Originally, you had ALLEXCEPT('Child', Child[Month]) which counted a table of ALL children, EXCEPT keeping current month filters.  This gave results that were counting children from all departments, though clearly we want results that still respect the current department filters. 

I tried using ALLEXCEPT('Parent','Parent'[Department]), but that gave the same result in each row, since getting ALL of the Parent table EXCEPT keeping filters on Department, would clearly combine all the months.   

 

So I combined them! I changed your ALLEXCEPT measure to this:

Count overall_allexcept = CALCULATE(COUNT('Parent'[Code]),ALLEXCEPT('Parent', 'Child'[Month], 'Parent'[Depart]))

Which gave me the results you showed in your expected results section.  You're getting ALL rows of Parent, except you keep filters on Month (so that the child results are correct. The parent Depart row doesn't have any month filters, so it counts everything) and filters on Depart (so that you're not adding in values from other departments for each month subtotal) 

 

If you have further questions, feel free to post here @Anonymous 

Anonymous
Not applicable

hi @Cmcmahan ,

 

Thanks for this solution, this works well with the kind of scenario in the pbix file. But since due to many different tables joined in the cube and with date in different table im still getting wrong count. Have dropped a brief explanation of the actual scenrio to you. If possible please let me know about the same.

 

Once again thanks for taking time for getting a solution for the issue. 

Thanks & Regards,

Praveen 

parry2k
Super User
Super User

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


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

Anonymous
Not applicable

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

tex628
Community Champion
Community Champion

Can you do a

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

Department being the column dimension that your using. 


Connect on LinkedIn
Anonymous
Not applicable

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
Community Champion
Community Champion

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!


Connect on LinkedIn
Anonymous
Not applicable

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. 

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

tex628
Community Champion
Community Champion

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 🙂


Connect on LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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