Forum Discussion
help summing two categories in a variable with corresponding values
Hi Friends!
Just a quick question for summing two values.
My goal is: to create total cost value.
usually to create a new column we use:
column = table[variable] + table[variable]
but this gets slightly tricky with my data.
Current data is like this:
table called 'costs' and below are some variables in the table:
| date | type | value | uniqueID |
| 1/5/2020 | expenditure | $10 | 1 |
| 1/7/2020 | forecast | $20 | 1 |
| 1/6/2020 | expenditure | $50 | 2 |
| 1/8/2020 | forecast | $20 | 2 |
| 1/4/2020 | expenditure | $20 | 1 |
so I want to create a new measure called total cost where each value which is expenditure is summed with each value that is a forecast for its relevant corresponding uniqueID.
so far I have:
total cost = costs[type] = "expenditure" + cost[type] = "forecast"
but I don't know where each corresponding of the values comes in to be summed together... any help much appreciated!!
thanks in advance for all the shared tips and advice!!
Hi, Anonymous
According to your description and sample data, I think you can try this measure to achieve your requirement:
total cost = CALCULATE( SUM('costs'[value]), FILTER(ALLSELECTED('costs'), [type] in {"expenditure","forecast"}&& [UniqueID]=MAX([UniqueID])))Then create a table chart and place the column and measure like this:
And you can get what you want.
You can download my test pbix file below
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Anonymous , Create a new column like
sumx(filter(Table, [type] =earlier([type])), [value])
Thanks so much for your guidance amitchandak !!
I wrote it as:
sumx(filter(Costs, Expenditure =earlier(Forecast)), Value) but unfortunately it didn't work 😞
The error appeared saying 'the syntax for ')' is incorrect
Really appreciate your time and help!!
Hi, Anonymous
According to your description and sample data, I think you can try this measure to achieve your requirement:
total cost = CALCULATE( SUM('costs'[value]), FILTER(ALLSELECTED('costs'), [type] in {"expenditure","forecast"}&& [UniqueID]=MAX([UniqueID])))Then create a table chart and place the column and measure like this:
And you can get what you want.
You can download my test pbix file below
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
