Calculate value from a summarize table

[JorgeMolano]

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 😄

[camargos88]

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

[v-xuding-msft · Accepted solution]

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]

Hi,

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

Thanks so much for you'r help !!😉

[JorgeMolano]

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]

Hi @JorgeMolano ,

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

Ricardo