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)

     

v-chenwuz-msft's avatar
v-chenwuz-msft
Community Support
4 years ago

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.