Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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;

IDBudgeted CostTypePeriodValueDate
0101nullCapexFY20 PE11 - Feb 20402.1101/02/2020
0101nullOpexFY20 PE11 - Feb 20549.9901/02/2020
0101124999,99CapexTotal001/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's avatar
    Anonymous
    6 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

  • 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's avatar
      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?

      • V-lianl-msft's avatar
        V-lianl-msft
        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's avatar
    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!