cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
Anonymous
Not 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!

5 REPLIES 5
Anonymous
Not 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!

Super 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

Anonymous
Not 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?

Community 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.

Anonymous
Not applicable

Hi Liang,

I'm really stuck at an impass here!

Announcements

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors