Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
  • Russell-PBI's avatar
    Russell-PBI
    4 years ago

    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)

     

Russell-PBI's avatar
Russell-PBI
Resolver II
4 years ago

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.