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