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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
hpatel247
Helper I
Helper I

Add a value to an already calculated column

Hi,

 

In my table, i have the following columns:

 

Client ID, Category, Daily Cost, Duration

 

I have created a measure the calculates total cost by using Daily Cost * Duration.

 

This part is fine, however i now need to add specific values to some categories.

 

e.g Category A has total cost of £100,000

Category B has total cost of £250,000

Category C has total cost of £430,000

Category D has total cost of £57,000

 

I need to add, £250,000 to Category C & £40,000 to Category D as these are one-off costs

 

I tried changing the formula to show as =IF(Category="C",(Daily Cost * Duration)+£250,000),IF(Category="D",(Daily Cost * Duration)+£40,000),Daily Cost * Duration)

 

However, what this is doing in multiplying the amount by the number of clients under each category.

 

I would like my end result to be as:

 

Category A = £100,000

Category B = £250,000

Category C = £680,000

Category D = £97,000

 

Is there anyway round this?

 

kind regards

 

Hetal

2 REPLIES 2
v-jiascu-msft
Microsoft Employee
Microsoft Employee

Hi @hpatel247,

 

Could you please mark the proper answers as solutions?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-jiascu-msft
Microsoft Employee
Microsoft Employee

Hi @hpatel247,

 

Seems you used the formula in a calculated column. So the one-off value will be added to every row of the category respectively. I would suggest you use a measure instead. It could be like below.

Measure =
IF (
    MIN ( 'table'[Category] ) = "C",
    SUMX ( 'table', 'table'[Daily Cost] * 'table'[Duration] )
        + 250000,
    IF (
        MIN ( 'table'[Category] ) = "D",
        SUMX ( 'table', 'table'[Daily Cost] * 'table'[Duration] )
            + 40000,
        SUMX ( 'table', 'table'[Daily Cost] * 'table'[Duration] )
    )
)
Measure =
IF (
    MIN ( 'table'[Category] ) = "C",
    SUM ( 'table'[calculated column already exist] ) + 250000,
    IF (
        MIN ( 'table'[Category] ) = "D",
        SUM ( 'table'[calculated column already exist] ) + 40000,
        SUM ( 'table'[calculated column already exist] )
    )
)

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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