Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Calculation within column

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:    Mun...
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    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:


     


     

FreemanZ's avatar
FreemanZ
Super User
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

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!