How to Sum based on an if statement

[Mark_Timson]

Hi all,

I was wondering if it was possible to do something similar to the below in power bi

IF Table.Col = "Y" THEN SUM(Table1.Col) ELSE IF Table.Col = "N" THEN SUM(Table2.Col) ELSE 0.

I want to change the column I am summing on based on its related value in another table in this case when it = Y or = N.

Your help would be appreciated.

[AkhilAshok]

Tough to answer this without seeing the datamodel or how you use the measure in report. Is Table[Col] a madatory single value filter in report? If yes, then you could simply do it with a measure:

Measure =
SWITCH (
    SELECTEDVALUE ( Table[Col] ),
    "Y", SUM ( Table1[Col] ),
    "N", SUM ( Table1[Col] ),
    0
)

If this is not the case, and you want the measure to be dynamically based on value in the Table[Col], then you could try as below (assuming you have a One-to-Many relationship between Table -> Table1 & Table -> Table2):

Measure =
VAR MeasureY =
    CALCULATE ( SUM ( Table1[Col] ), Table[Col] = "Y" )
VAR MeasureN =
    CALCULATE ( SUM ( Table2[Col] ), Table[Col] = "N" )
RETURN
    MeasureY + MeasureN

[Mark_Timson]

Hi Akhil,

Thanks for replying. My result set isnt actually based off of these y or n values its more like an expression on the data. Its originally from business objects. Below is an extract from what I am trying to reproduce in power bi. 

[AkhilAshok]

Have you tried the 2nd option which I mentioned?

[Mark_Timson]

Hi, 

Im not trying to sum both together Im trying to return one or the other based on the data in the table. 

[v-piga-msft]

Hi @Mark_Timson ,

If it is convenient, could you share your data sample and your desired output so that we could help further on it?

Best  Regards,

Cherry

[AkhilAshok]

What is the type of object in BO Universe? Is it created as a measure or dimension or detail? If it is created as a measure, and if the Projection setting of the object is Sum, then it should work the same way as I showed using DAX, since the entire logic gets summed up at the report level (both Y and N cases).

[Gordonlilj]

Hi,

You could try:

SWITCH(TRUE(),Table.Col="Y",SUM(Table1.Col),Table.Col ="N",SUM(Table2.Col),0)

[Mark_Timson]

This didnt work as the column that is being filtered is not related I think.