Forum Discussion
Return Value based on two text conditions
Hi,
This maybe a very simple solutions, but currently I have a table like below with some sample data;
| ID | Budgeted Cost | Type | Period | Value | Date |
| 0101 | null | Capex | FY20 PE11 - Feb 20 | 402.11 | 01/02/2020 |
| 0101 | null | Opex | FY20 PE11 - Feb 20 | 549.99 | 01/02/2020 |
| 0101 | 124999,99 | Capex | Total | 0 | 01/02/2020 |
Now what I need to do is a calculation over the entire table, to return Budgeted Cost, where the conditions match, for example, [Type] = Capex and [Period] = Total. However I am currently trying to calculate all the Values and add a filter on the calaculate like
Capex Total TD = CALCULATE(Mesaure), Table[Type] = "Capex", [Period] = "Total"
If i use the above it returns no result
I then added an all filter as there is a slicer that looks at a particular month;
Capex Total TD = CALCULATE(Mesaure), Table[Type] = "Capex", [Period] = "Total", ALL(Table)
When I use this, it returns all the values as the same (there are over a thousand id.
Any help is greatly appreciated
thanks
- Anonymous6 years ago
Hi Guys
One thing I ommitted previously was that there was a slicer that singled some results to a particular period.
This calculation was taking the whole period, and I added the ALL() funtion to the end looking up the date table, By adding the ALL() funtion straight after the SUM() meant I got the solution I was looking for.
Thank you so much for all your efforts in helping, all of the information provided has been useful!
All the best, and happy data naughting!
5 Replies
- amitchandakSuper User
I hope you have to give like
Capex Total TD = CALCULATE(sum(table[Budgeted Cost], Table[Type] = "Capex", [Period] = "Total"))Null is blank not "NULL" the text and data type is numeric
- AnonymousNot applicable
Thanks so much for replying, I did in the time since posting try that, and I just get no result (literally blank). This issue has been bugging for a few days I have no idea what else to try. I mean I have created a column based on those conditions to return the result, is it possible to return the value of the column?
- V-lianl-msftCommunity Support
Hi Anonymous ,
Have you tried the if statement?
Capex Total TD = IF(MAX(Table[Type]) = "Capex"&&MAX(Table[Period]) = "Total",[measure])Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- AnonymousNot applicable
Hi Guys
One thing I ommitted previously was that there was a slicer that singled some results to a particular period.
This calculation was taking the whole period, and I added the ALL() funtion to the end looking up the date table, By adding the ALL() funtion straight after the SUM() meant I got the solution I was looking for.
Thank you so much for all your efforts in helping, all of the information provided has been useful!
All the best, and happy data naughting!