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

      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-for-total-and-subtotals-in

      • tex628's avatar
        tex628
        Community Champion
        7 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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,Praveen 

       

      • tex628's avatar
        tex628
        Community Champion
        7 years ago

        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!