Forum Discussion

JorgeMolano's avatar
JorgeMolano
Frequent Visitor
6 years ago
Solved

Calculate value from a summarize table

Hi community, I trying to build a Table Heatmap like the following image,     But to achieve this I don´t get any way to calculate the "% NOI" value whit the data set I have. This is an exa...
Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi camargos88 ,

    Maybe this can help you. You could reference it to modify your formula based on the actual situation.

    Income =
CALCULATE (
    SUM ( 'Table'[Total] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Category], 'Table'[Month] ),
        'Table'[Column] = "Income"
    )
)

Expense =
CALCULATE (
    SUM ( 'Table'[Total] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Category], 'Table'[Month] ),
        'Table'[Column] = "Expense"
    )
)

NOI = [Income] + [Expense]

% NOI = DIVIDE([NOI],[Income])

     

    And you could take a look at the document: Use conditional formatting in tables to try customizing the background color.

    Conditional formattingResult

    You could download the attachment to learn more details.

camargos88's avatar
camargos88
Community Champion
6 years ago

Hi JorgeMolano ,

 

Try this code:

 

NOI =
VAR _noi_INCOME = CALCULATE(SUM('Table (2)'[Total]); 'Table (2)'[Column] = "Income")
VAR _noi_EXPENSE = CALCULATE(SUM('Table (2)'[Total]); 'Table (2)'[Column] = "Expense")

RETURN ((_noi_INCOME + _noi_EXPENSE) * 100) / _noi_INCOME / 100
 
Ricardo
  • JorgeMolano's avatar
    JorgeMolano
    Frequent Visitor
    6 years ago

    Hi camargos88

     

    I add this new column but it looks like only get 1 or ∞ when is an Expense, and when I try to build the Table Heatmap only appears the  ∞ symbols.

     

     

    Regards!

    • camargos88's avatar
      camargos88
      Community Champion
      6 years ago

      Hi JorgeMolano ,

       

      It should be a measure, I won't work as calculated table.

       

      Ricardo

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    6 years ago

    Hi camargos88 ,

    Maybe this can help you. You could reference it to modify your formula based on the actual situation.

    Income =
CALCULATE (
    SUM ( 'Table'[Total] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Category], 'Table'[Month] ),
        'Table'[Column] = "Income"
    )
)

Expense =
CALCULATE (
    SUM ( 'Table'[Total] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Category], 'Table'[Month] ),
        'Table'[Column] = "Expense"
    )
)

NOI = [Income] + [Expense]

% NOI = DIVIDE([NOI],[Income])

     

    And you could take a look at the document: Use conditional formatting in tables to try customizing the background color.

    Conditional formattingResult

    You could download the attachment to learn more details.

    • JorgeMolano's avatar
      JorgeMolano
      Frequent Visitor
      6 years ago

      Hi,

       

      This is exactly what I was looking for, now I know how to use this calculations.

      Thanks so much for you'r help !!😉