Calculated field with filter on one component only

[awu3639]

In one table, I have a number of columns such as total commission paid and # units sold. # units sold can be broken down by another column: marketed vs not marketed to track whether or not these units were sold because of marketing methods. Sometimes commission is paid for non-marketed because it is tracked incorrectly. I want to create a simple field that takes total commission paid divided by the # units sold due to marketing. I played around with a number of codes in PowerBI and I cannot get it to work. How would I filter the # units sold by marketing and keep total commission paid all in one field?

[v-jiascu-msft · Accepted solution]

Hi @awu3639,

If you have a data table as the picture showed, you can try these two formulas below.

CalculatedColumn =
SUM ( Table1[total commission] )
    / CALCULATE (
        SUM ( Table1[units sold] ),
        FILTER ( 'Table1', Table1[tag] = "marketed" )
    )

Measure =
SUM ( Table1[total commission] )
 / CALCULATE ( SUM ( Table1[units sold] ), 'Table1'[tag] = "marketed" )

Best Regards!

Dale

[awu3639]

Thanks Dale, worked perfectly!