Matrix Total does not filter, does not reflect what it is totaling.

[psmith-nhs-inc]

I went from one weird situation to another. 

As you can see in the screenshot, while the individual rows are accurate, the total does not represent the total.  It is as simple as that.  I don't see how this is anything I have control over.

MonthlySalesGoal is not even a measure, just a column in a table.

This total is valid for all TerritoryNames,  but obviously,  I am filtering it out to 5 of them, and the totals should reflect that.

Phil

[GilbertQ]

Hi @psmith-nhs-inc

I have come across a similar situation and it was where my subtotals did not add up to the rows in the column.

The reason for my issue was because the filter context of my measure that I created worked fine in the row context, but at a subtotal level there is no row context, so the measure appears to display the incorrect results.

I read this great blog post by the people from Power Pivot Pro, where they explains why it happens and how to resolve the issue.

Subtotals and Grand Totals That Add Up “Correctly”

I am sure that it can solve your issue, if you get stuck or it does not make sense let us know.

[psmith-nhs-inc]

I was able to cut out almost everything, and still get it to screw up, so here is the cut down PBIX.  I do not think the the link suggested applies, at least not in terms of dates.  Link to PBIX below the picture.

Thanx to anyone who can take a look.

https://dl.dropboxusercontent.com/u/9936354/SalesRepDEVpms4-17-17xxx.pbix

[GilbertQ · Accepted solution]

Hi @psmith-nhs-inc

I found the issue with your matrix.

It was not to do with the measures but rather with the way the relationships were setup.

You were using the Users table and a value from the MonthlyGoals table, but they were not linked together correctly. So due to this relationship not being in place the total being shown was correct based on the filter context (because there was no filtering being applied between the Users table and the MonthlyGoals table)

So what I did to resolve this was to change the Cross Filter direction on the Customer table to the Territory Table to be Both. 

And now the total displays correctly.

I also put in another measure called Correct Total if you were wanting to have a different measure based on the Row Context.

You can find the uploaded file here: https://1drv.ms/u/s!Apxn-69XhcAmhqhy5yutRFAH92ID6Q 

[alicebotteri]

Hi Guys, 

I''ve the same issue.. How can I make a measure based on row contex ? I need to show the total, which it hasn't to change when I filtered on a dimension.

Thanks

Alice 

[psmith-nhs-inc]

Thank you.  I am unable to download from your link, but I made the relationship change and it appears to work.  Now I have to check everything else to see if it is still working. 

I wish I had a resource that truly explained relationships and filtering in the Power BI context.  There is something that is not translating from the standard Relational database model for me.  I am not sure why your fix worked or what was wrong.  I will be spending some time trying to figure it out, though.

Do you know of a good resource that really delves into relationships and filters for BI? 

Could you also send me the measure you were talking about?  I don't know if I need it but it might help me to understand what was going on.

Anyway, thank you very much.  This does solve the problem.  Now to figure out exactly why...

Phil

[GilbertQ]

Hi @psmith-nhs-inc

I did check the download file and it does work when I tested it by using Chrome Incognito Mode https://1drv.ms/u/s!Apxn-69XhcAmhqhy5yutRFAH92ID6Q

I hope that I can explain this well enough for you, so that you can understand why it worked. Coming from creating data warehouses and using databases with related tables, hopefully I can explain.

Typically in a relational database you have a Primary Key -> Foregin Key relationship where the keys match between the tables. So when you want to related data between the tables you join the tables and use the Keys as part of the join

EG: Select * from TableA as A, Inner join TableB as B on A.Key = B.Key

With the above the direction is always single, meaning that it goes from TableA to TableB, as far as I am aware there currently is not a filter direction going both ways in a relational database context.

Now with Power BI, you can have a cross filter direction of single - Which represents the above.

But you can also have a cross filter direction of both - What this means is that when you have a table relationship created and the cross filter direction is set to both, the data can flow both ways. So this means that when you have another table linked to your table that has the cross filter direction set to both, this effectively means that the data can flow through your table to the underlying table. If put another way when the table cross filter direction is set to both, the data can then flow through this table and get data from another related table.

Now in your Power BI Desktop file, you had Customer in your Matrix as well as a value from the MonthlyGoals table. 

The issue here is that there was no direct relationship between Customer and MonthlyGoals table, because all the Cross Filter directions were set to single (Which is best practise and the right thing to do)

So as you can see below if you look there is no way to get the correct details from the Customer table. The relationship from the Territory Table to the Customer Table is set to single and is a one to many (with the many going to the Customer table). So when you filter the data it goes to the Territory table, but because of the cross filter direction set to single it stops at the Territory table.

Now when you change the Cross Filter direction on the Customer table to Both, what now happens is the data from the Customer Table, can flow through the Territory table to your MonthlyGoals table. As you can now see with the boxes highlighted in RED

Now because all 3 tables are related it will bring through the correct values.

I know that this is quite long, but please let me know if this helps?

And here is a link from the Power BI Documentation which might assist futher: Create and manage relationships in Power BI Desktop

[Anonymous]

---

@psmith-nhs-inc wrote:

I wish I had a resource that truly explained relationships and filtering in the Power BI context. 

---

Hi Phil, 

A little bit of resource on Bi-directional cross filter that applies to power BI:

http://download.microsoft.com/download/2/7/8/2782DF95-3E0D-40CD-BFC8-749A2882E109/Bidirectional%20cr...

[GilbertQ]

Hi there.

I am glad it's working for you. I will send a better explanation of why it works as well as the other calculation on Monday. I did test the link to make sure it works. Will also hopefully fix that on Monday

[psmith-nhs-inc]

I look forward to Monday.  And one more question, if I am not imposing too much, "your help here is greatly appreciated,):

Why was my total off, not being filtered properly, but the individual line items were being filtered properly?  Is there something about the totals row/function that is fundamentally different?

Thank you again.