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 example of my data set, as you can see the calculation need to be form the sum of all the Income of the Month by Categry and then obtain by simple rule of three the "% NOI".

 

 

 

 

¿Is there any way that I can create a calculete field insede my data set or some other methods to perform this?

Thanks 😄

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.

5 Replies

  • camargos88's avatar
    camargos88
    Icon for Community Champion rankCommunity 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!

    • v-xuding-msft's avatar
      v-xuding-msft
      Icon for Community Support rankCommunity 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 !!😉