Return Value based on two text conditions

[Anonymous]

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

[Anonymous · Accepted solution]

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!

[amitchandak]

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

[Anonymous]

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-msft]

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.

[Anonymous]

Hi Liang, 

Thanks for you're answer, Howvever I am still getting no results from this solution. 

I'm really stuck at an impass here!