Forum Discussion

awu3639's avatar
awu3639
Frequent Visitor
8 years ago
Solved

Calculated field with filter on one component only

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 unit...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    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

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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's avatar
    awu3639
    Frequent Visitor
    8 years ago

    Thanks Dale, worked perfectly!