Forum Discussion
Power BI Division problem
Hi ,
I am working on Power BI Desktop to find ratio. Basically division of two measure.
Here is the measure:
Measure Is Excluding AOC =
SUMX(
'table1',
IF(
'table1'[AOC_TYPE] <> "Not AOC" &&
NOT(ISBLANK('V_DW_SB_2024_20240627'[SCHEDULED_MONTH])),
0,
1
)
)
Measure Is Excluding AOC and NoStat =
SUMX(
'table1',
IF(
'table1'[AOC_TYPE] = "Not AOC" &&
'table1'[NO_ACCESS_STATUS] <> "Contractor Fail" &&
NOT(ISBLANK('V_DW_SB_2024_20240627'[DATE_COUNTED])) &&
'table1'[STATUS] IN {"No Longer in Scope", "v1 Complete", "v1 No Access", "v1 No Clearance Required", "v1 No Structure Found", "v1 Partial Clearance"},
1,
0
)
)
Result for Measure Excluding AOC:
Result for Measure Excluding AOC and NotStat:
Ratio Result by manual calculation in Excel:
Ratio= Measure Excluding AOC and NotStat/Measure Excluding AOC
Power BI incorrect result:
Power BI Formula:
DivideResult3 = DIVIDE([Measure Is Excluding AOC and NoStat],[Measure Is Excluding AOC])
Please help me to find out how I can find the correct result for finding ratio formua
2 Replies
Please compare and check xl used code and Power BI used code both same or not ?
Please double check in this below meausre conditions out come 1, 0 in right order or not? I feel like it should be 0,1.
Measure Is Excluding AOC and NoStat =SUMX('table1',IF('table1'[AOC_TYPE] = "Not AOC" &&'table1'[NO_ACCESS_STATUS] <> "Contractor Fail" &&NOT(ISBLANK('V_DW_SB_2024_20240627'[DATE_COUNTED])) &&'table1'[STATUS] IN {"No Longer in Scope", "v1 Complete", "v1 No Access", "v1 No Clearance Required", "v1 No Structure Found", "v1 Partial Clearance"},1,0))If Still issue not resolved then start code debugging by comment one by one conditions in second Measure?
Good Luck!
Still struggling with the same issue. I tried changing the sequence for 0 and 1 in the formula
