Calculation within column

[Anonymous]

Hello, 

I have data on municipality expenditures by year. There are also variables for the class of expenditure e.g. library services, and the account this expenditure is on. Example: 

Municipality	Expenditure	Account	Account index	Class	Year
Municipality 1	1000€	Total costs	1	Library services	2021
Municipality 1	100€	Staff costs	2	Library services	2021
Municipality 1	100€	Grants	3	Library services	2021
Municipality 2	3000€	Total costs	1	Administration	2021

There are only three possible accounts per each municipality/year/class. The problem is, the "total costs" sum includes the other account costs, and the row sum per class or per municipality will sum up to more than what are the true total costs. So what I want to do is to create a new column with new accounts: staff costs, grants, and "other costs", where staff costs and grants are subtracted from the total costs in order to obtain "other costs". My strongest idea is to index the rows by municipality/class/year, and create "other costs" column which references the sorted Account -row and subtracts 2 and 3 from 1 and places this on the index 1 row. On the index 2 row, i want to place the unchanged staff costs etc, so that the row sum will then reflect the total cost. There are plenty of instructions on how to create differences between consecutive rows, but not on more complex calculations. I am stuck on this since I am new to powerBI.

Any help is greatly appreciated, thanks. 

[FreemanZ · Accepted solution]

Hi @Anonymous 

i am not sure if i understand your requirement complete, please try to create a table with this:

Table =

VAR _table1 =

    SUMMARIZE(

        TableName,

        TableName[Municipality],

        TableName[Class],

        TableName[Year],

        TableName[Account],

        TableName[Account index]

    )

VAR _table2 =

ADDCOLUMNS(

    _table1,

    "Expenditure",  

    IF(

        TableName[Account index]=1,

        CALCULATE(

            SUM(TableName[Expenditure]),

            TableName[Account] = "Total costs"

        )-

        CALCULATE(

            SUM(TableName[Expenditure]),

            TableName[Account] <> "Total costs",

            VALUES(TableName[Account index])

        ),

        MIN(TableName[Expenditure])

    )

)

RETURN _table2

I tried and it worked like this:

[Anonymous]

Hi @FreemanZ, Your table seems to do exactly what I want. I also managed to solve this by creating a new column and using switch and several filters. Thanks for the help anyway!