Forum Discussion
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 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.
8 Replies
- Hi,You could try:
SWITCH(TRUE(),Table.Col="Y",SUM(Table1.Col),Table.Col ="N",SUM(Table2.Col),0)
This didnt work as the column that is being filtered is not related I think.
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 + MeasureNHi 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.
Have you tried the 2nd option which I mentioned?
