Forum Discussion
My Matrix Row Values are Incorrect
So it seems this topic has been addressed on several occasions, but I will admit that some of the solutions are hard to follow. I created this matrix table, and while the bottom total is correct, the row columns are not.
I know it has to do with the context of the data, but can someone advise me on how to fix this issue?
Here is the solution I came up with:
Using as a filter value:
Max Obligated Flag = VAR MaxObligatedValue = CALCULATE(MAX(Table1[Obligated]), ALLEXCEPT(Table1, Table1[Record Number], Table1[Type of Mod])) RETURN IF(Table1[Obligated] = MaxObligatedValue, TRUE, FALSE)Or, using as a grouping attribute:
Max Value by Record Number = VAR MaxObligated = CALCULATE(MAX(Table1[Obligated]), ALLEXCEPT(Table1, Table1[Record Number], Table1[Type of Mod])) RETURN CALCULATE(MAX(Table1[Value]), ALLEXCEPT(Table1, Table1[Record Number], Table1[Type of Mod]), Table1[Obligated] = MaxObligated)
9 Replies
Hi Anonymous, it's a bit difficult to discern with just the table given. Can you provide a model diagram and sample of the data?
In a lot of cases where the total sums up differently to the sum of the individual lines, it is because of the relationship between tables. For example, one record in your measure table may be related to more than one record in your attribute table and those records have different 'Obligated value' values (e.g., $0-$25K and $25K-$250K). The total is unaffected by the attribute, so just counts the number of records in the actual table, whereas the rows are affected by the attribute and will be counted against each attribute value where there is a relationship.
Hi Anonymous
What is the measure?
Anonymous
This is why they're not the same. You are expected to have more in the raws than in the grand total. You have values repeated in different rows because DISTINCT will be applied on each cell seperately but this does not happen at the gand total where the distinct count will be curried over all values which is similar to taking the distinct count of all other cells therefore you will allways have less in the grand total
Hi Russell-PBI, please see the attached. I had to clean up my client's data to a dummy version, but these are the same filters that I applied. The total value is fine, but as you can see the filters aren't being applied at the row level.
That's a strange one. From the screenshot alone, I don't see any reason why the rows wouldn't sum up to the total. If it's now dummy data, would you like to share your PBI file and data, and I'll see if I get the same result? You can send me a DM if you'd prefer.
Here is the solution I came up with:
Using as a filter value:
Max Obligated Flag = VAR MaxObligatedValue = CALCULATE(MAX(Table1[Obligated]), ALLEXCEPT(Table1, Table1[Record Number], Table1[Type of Mod])) RETURN IF(Table1[Obligated] = MaxObligatedValue, TRUE, FALSE)Or, using as a grouping attribute:
Max Value by Record Number = VAR MaxObligated = CALCULATE(MAX(Table1[Obligated]), ALLEXCEPT(Table1, Table1[Record Number], Table1[Type of Mod])) RETURN CALCULATE(MAX(Table1[Value]), ALLEXCEPT(Table1, Table1[Record Number], Table1[Type of Mod]), Table1[Obligated] = MaxObligated)
Hi Anonymous ,
Is that you want to the total should be all of the table unless the part of which type is new/recompete. But the new/recompete column values is right.
Maybe you can try to use HASONEVALUE to control the total column. Some measure like the following and replace the values of the martix table:
Measured = IF( HASONEVALUE( 'table'[Type of Mod] ), DISTINCTCOUNT( 'table'[Record Number] ), CALCULATE( DISTINCTCOUNT( 'table'[Record Number] ), ALLEXCEPT( 'table', 'table'[Obilgated Value] ) ) )If you need more help, please share sample date and expect result.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
