Forum Discussion

ankit_cd's avatar
ankit_cd
Regular Visitor
4 years ago
Solved

Calculating column values based on two columns and problem in fetching last row's calculation

Hello,

A newbie to Power BI here. I am facing a calculation problem and not sure where I am going wrong. I am trying to create a new column in a separate table where the idea is to calculate sum (total cost) for a particular 'year' and a particular 'type of cost'.

 

 

For example, from above table, I want to generate a single value of cost (Approved in Tariff Order) for a single year (2010) and a single type of cost (fixed). Currently I have input the following formula and it gives me the accurate sum but only for (n-1) years. The calculation for last year is somehow not being generated 

 

Fixed Costs  =
    CALCULATE(
        SUM(ARR_Approved[Approved in Tariff Order]),
        FILTER(ARR_Approved,ARR_Approved[Year]=APPC_Calculated[Year]),
        FILTER(ARR_Approved,ARR_Approved[Type of Cost]="Fixed")
    )
 
See the output column generated in Power BI as below:

 

Appreciate all the help I can get here. Thank you 🙂

CALCULATE
DAX
Multiple columns
power bi
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi ankit_cd ,

    Since you did not give a specific table, I had to create my own table for testing according to your description, please point out if there are any problems.

    Please try below steps:

    1. Below is my test table

    Table1:

    Table2:

    2. In "Table2", add a new column with below dax formula

     

    Total Cost =
VAR cur_year = Table2[Year]
VAR type_cost = "Fixed"
VAR tmp =
    FILTER (
        ALL ( Table1 ),
        Table1[Type of cost] = type_cost
            && YEAR ( Table1[Year] ) = cur_year
    )
RETURN
    SUMX ( tmp, [Cost] )

     

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_ Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi ankit_cd ,

    Since you did not give a specific table, I had to create my own table for testing according to your description, please point out if there are any problems.

    Please try below steps:

    1. Below is my test table

    Table1:

    Table2:

    2. In "Table2", add a new column with below dax formula

     

    Total Cost =
VAR cur_year = Table2[Year]
VAR type_cost = "Fixed"
VAR tmp =
    FILTER (
        ALL ( Table1 ),
        Table1[Type of cost] = type_cost
            && YEAR ( Table1[Year] ) = cur_year
    )
RETURN
    SUMX ( tmp, [Cost] )

     

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_ Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.