Forum Discussion

Mark_Timson's avatar
Mark_Timson
Helper I
7 years ago

How to Sum based on an if statement

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 ...
Need Help
AkhilAshok's avatar
AkhilAshok
Solution Sage
7 years ago

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's avatar
Mark_Timson
Helper I
7 years ago

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's avatar
    AkhilAshok
    Solution Sage
    7 years ago

    Have you tried the 2nd option which I mentioned?

    • Mark_Timson's avatar
      Mark_Timson
      Helper I
      7 years ago

      Hi, 

       

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

      • AkhilAshok's avatar
        AkhilAshok
        Solution Sage
        7 years ago

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