Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
Hi @hpatel247,
Could you please mark the proper answers as solutions?
Best Regards,
Dale
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
User | Count |
---|---|
122 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
189 | |
96 | |
67 | |
63 | |
53 |