Power BI Division problem

[nishthabhakta20]

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

[VN999]

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!

[nishthabhakta20]

Still struggling with the same issue. I tried changing the sequence for 0 and 1 in the formula