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 corre...
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 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)You're awesome! This was very helpful!