Calculated field with filter on one component only

Question

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&nbsp;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 Answer

Hi&nbsp;awu3639,&nbsp;If you have a data table as the picture showed, you can try these two formulas below.&nbsp;&nbsp;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" )
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Best Regards!Dale

Forum Discussion

Calculated field with filter on one component only

2 Replies

Related content

Calculating with Filters

Calculate Filters

Matrix component - order question

How to search specific word in table filter Calculations

reusable components in reports