Forum Discussion

dtm644's avatar
dtm644
Frequent Visitor
7 years ago
Solved

Matrix - Subtract Row value from Total

I'm working in a matrix visual right now. How do I go about subtracting a row from the column total? 

 

KPI is a measure. 

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi dtm644 

    It seems the row the column totals subtract is the min value per column header.

    So create a measure to get the min value per column header

    Measure = MINX(ALLEXCEPT(Table1,Table1[column header]),[kpi])

    Then create a new measure to replace the KPI measure

    Measure 2 = IF(HASONEVALUE(Table1[state]),[kpi],[kpi]-[Measure])

    If the column total on your side is correct as you expected, the formula above should get your expected result.

    In my test, column total sums kpi values.

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    Hi dtm644 ,

     

    When you refer that you want to subtract the row from column total do you mean that the KPI total should be blank? Or another type of result?

     

    You should do something like:

    KPI = IF (HASONEFILTER(Table[Client_State]); [KPI]; BLANKS())

    Regards,

    MFelix

     

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi dtm644 

    It seems the row the column totals subtract is the min value per column header.

    So create a measure to get the min value per column header

    Measure = MINX(ALLEXCEPT(Table1,Table1[column header]),[kpi])

    Then create a new measure to replace the KPI measure

    Measure 2 = IF(HASONEVALUE(Table1[state]),[kpi],[kpi]-[Measure])

    If the column total on your side is correct as you expected, the formula above should get your expected result.

    In my test, column total sums kpi values.

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.