I have a table with multiple columns which comes from different data sources. in which 2 columns are Actual Number and Expected Number(both are measures). I have to create a new column in the table which just shows Actual Number/Expected Number.
Few rows have blanks for both Actual and Expected, for which i have to show "N/A". I was able to acheive the first part where we just show actual/expected, But challenge is when changing the logic to show N/A for blank values of actual and expected columns.
what i tried:
Created a measure,This works perfectly for non-blanks
IF([Actual] >0,[Actual]&"/"&[Expected])
I changed the measure to this to show N/A for blanks:
IF(ISBLANK([Actual]) ||ISBLANK([Expected]),"N/A",IF([Actual] >0,[Actual]&"/"&[Expected]))
when i try the above, the no of rows in the table drastically increases creating duplicates and i get many records with "N/A", where i only have 1 row which has blank actual/expected. Im unsure what is causing the issue and want to know what is the best way to approach this. Thanks in advance
