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)

     

tamerj1's avatar
tamerj1
Community Champion
4 years ago

Hi Anonymous 

What is the measure?

Anonymous's avatar
Anonymous
Not applicable
4 years ago

The measure is the distinct count of transactions that are new/recompetes

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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