Custom Column sumif

[Anonymous]

I am trying to create a Gross Margin Mix- for this I would use the following calculations

GrossMargin(per Row) / The Sum of all comparable Gross margins

I have a column with Gross Margin and a Column stating whether the item is comparable (Yes/No) 

The formula can either be based on CY or PY.

Thank you

[edhans · Accepted solution]

You should do this in Power BI with DAX, not Power Query. It will not perform well in Power Query as it isn't designed to dynamically filter tables. See below.

Some Measure = 
VAR varComparable = SELECTEDVALUE('Table'[Comparable])
VAR varNumerator = SUM('Table'[Gross Margin CY])
VAR varDenominator = 
    CALCULATE(
        SUM('Table'[Gross Margin PY]),
        'Table'[Comparable] = varComparable
    )
RETURN
    DIVIDE(varNumerator, varDenominator, BLANK())