Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
ankit_cd
Regular Visitor

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'.

 

Power BI Calculation Problem.png

 

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:
ankit_cd_0-1662467679581.png

 

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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:

vbinbinyumsft_0-1662518186698.png

Table2:

vbinbinyumsft_1-1662518207917.png

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] )

 

vbinbinyumsft_0-1662542517462.png

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.

View solution in original post

1 REPLY 1
Anonymous
Not applicable

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:

vbinbinyumsft_0-1662518186698.png

Table2:

vbinbinyumsft_1-1662518207917.png

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] )

 

vbinbinyumsft_0-1662542517462.png

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.