cancel
Showing results for
Did you mean:
Highlighted
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.

(has Both PBIX & the Sample Excel)

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

1 ACCEPTED SOLUTION

Accepted Solutions
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])```

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

12 REPLIES 12
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])```

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

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.

Thanks & Regards,

Praveen

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!

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

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

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

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:

 A1 A1 A1 BLANK()

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

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!

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.

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 110 members 1,556 guests
Recent signins: