Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 & Source

 

Need Help
  • parry2k's avatar
    parry2k
    7 years ago

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

12 Replies

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    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])
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        tex628
        Community Champion
        7 years ago

        Can you do a

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

        Department being the column dimension that your using. 

    • Cmcmahan's avatar
      Cmcmahan
      Resident Rockstar
      7 years ago

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

  • Cmcmahan's avatar
    Cmcmahan
    Resident Rockstar
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

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