Hi, I’m new with BI and also I study some solution in this forum relate to wrong grand total when using if, but stil fail so I need your support
I have column “SMS-Amt LM” create by measure, which showing only 1 month data
I have column “PSI by Amt” which take from table “ Dls PSI FY20 after” and from column “sellin target Amt”, which has data of 12 month
My origin Plan is create a measure showing “TargetSMScorrect” which follow the rule: if “ PSI by Amt” > “SMS Amt LM” the result return with value of “SMS AmtLM” if not, return with value “PSI by Amt” . Pls note that “PSI by Amt” i just pick up 1 month among 12 month by using filter from other separate drop down list in the report
. I try to apply the measure as below:
TargetSMScorrect =
sumx(
ADDCOLUMNS('Product Group',"SMS-Amt LM",'My measure'[SMS-Amt LM],"PSI by Amt",sum('Dls PSI FY20 after'[Sellin target Amt])),
if([PSI by Amt]>[SMS-Amt LM],[SMS-Amt LM],[PSI by Amt])
)
Each row give correct result But grand total still wrong and I guess the issue is because my fomula cannot understand I just Need to take the result of 1 month in “PSI by Amt” to compare with “SMS by Amt LM” but with my fomular, it will compare total 12 month and return the wrong grand total with value always equal with “SMS by Amt LM”
I dont know how to solve my issue, could you kindly support? Tks in advance!
