Forum Discussion

dscott73's avatar
dscott73
Resolver I
8 years ago

Divide a Measure by a Column Value?

I have some sales data where I calculate the MTD sales:

 

MTD Sales = TOTALMTD(SUM('Sales data'[Sales Total]), 'Date Table'[Date])

 

I have a SalesPerson table which holds the sales person's monthly quota.

 

I am trying to write something that functionaly does the following:

 

Monthly Quota % := DIVIDE([MTD Sales], 'Sales Person'[Monthly Quota])

 

Currently this works:

 

Monthly Quota % = DIVIDE([MTD Sales],  SELECTEDVALUE('SalesPerson'[MonthlyQuota]))

 

But, you must have the column MonthlyQuota displayed on the visualization (Grid in this case)

 

Any thoughts on how to do this?

Need Help

2 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    Hi dscott73

     

    Are you just trying to create a MTD Sales measure per salesperson?

     

    Do you have a relationship between your 'Sales data' and 'Sales Person' tables?

    • dscott73's avatar
      dscott73
      Resolver I
      8 years ago

      I have the MTDSales per salesperson (my MTDSales measure)

       

      On the Salesperson table there is a Quota column. The Monthly Quota column is 100,000 for each sales rep currently.

       

      I am trying to create a new measure which shows me the percentage of Quota met by MTDSales.

       

      IE: Mark as 100k sales quota each month.  His MTD Sales is currently 50k. He has met 50% of his Quota.

       

      I want to show the 50%

       

      To answer your question, yes there is a relationship between the SalesPerson table and the SalesData.  The SalesPerson table holds the Monthy Quota