Forum Discussion
Helper IIIMatrix Total does not filter, does not reflect what it is totaling.
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
Super User
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
Helper IIIThank 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
- GilbertQHi 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.
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:
- GilbertQ
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