Forum Discussion

hpatel247's avatar
hpatel247
Icon for Helper I rankHelper I
8 years ago

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

Need Help

2 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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