Chart Annual Quota as a daily value

[Breckenridge]

I have a large table of sales data, essentially recording each sale with the date it was made & the salesperson that made the sale.  Each row is a sale, and multiple sales occur daily.

I have a second table with the salesperson's data - name, office location, and importantly an annual sales quota.  The two tables are linked one to many by the salesperson.

I have a measure that will calculate my cumulative sales, and i can chart that easily by date, say for a year.  I would like to also chart against the cumulative total a line showing the quota as a straight line rising from zero to the annual amount by the of the year.

Each sales person has a different quota, so having a date table with the individual quotas by date seems impractical.

Any help would be appreciated thanks!

[Ashish_Mathur]

Hi,

Share a dataset and show the expected result.

[Breckenridge]

See below for sample, so in January Jim sold $15, Bob sold $35, and Jane sold $25.  

I am using this formula to calculate cumulative sales:

Split Revenue Cumulative = calculate(sum('SalesTable'[Amount of Sale]),filter(ALLselected('DateKey'[Date]),'DateKey'[Date]<=max('DateKey'[Date])))

where 'DateKey' is simply a table of daily dates.

I can plot the cumulative sales for the year, in the data below it would rise quickly for Jim at the start of January, and slowly for Jane.  

Their quotas I would like to plot alongside, as a straight diagonal line, starting in Jim's case with 1200/365 on Jan 1st, and 2*1200/365 on jan 2nd, etc, so Jim can see how he has been performing against a steadily rising quota.

SalesTable

Date                 Salesperson                   Amount of Sale

01-Jan-17	Jim	5
01-Jan-17	Jim	10
02-Jan-17	Bob	15
05-Jan-17	Bob	20
31-Jan-17	Jane	25

Quota Table

Salesperson          Annual Quota

Jim	1200
Bob	120
Jane	600

[v-yuezhe-msft]

@Breckenridge,

Do you want to show daily data of the whole January for each sales person? Could you please post expected result in table format?

Regards,
Lydia

[Breckenridge]

I have cumulative sales, summed up daily.  This chart here shows me plotting a monthly sum of cumulative sales, and a monthly sales amount.  What i'm missing is the quota - i have a value for the year in a table per sales person, but no way of plotting the quota as it "grows" linearly through the year.  ie I expect a sales person to have sales summing to 50% of their quota 6 months through the year. If they're behind, I'm worried, if they're ahead I'm happy.

[parry2k · Accepted solution]

Hey @Breckenridge I did the solution, you basically need to break down your quota per day and then use similar approach to do the running total on quota.

To break down quota per day is very easy and here are high level steps:

- open query edit

- add two column in your quota date Start Date and End Date, change type to date

- add Custom Column called "Quota/Day" and divide your [Quota] / 365 and change type to decimal number

- change type of your start date and end date column to whole number, add it as new step

- add new custom column called Date and use this {[Start Date]..[End Date]}

- you will notice a date column as list

- click expand arrows on date column and select "expand to new rows"

- change date column data type to date

- remove "quota, end date, start date" columns

- in the end you will have 3 columns, Salesperson, Quota/Day and Date

close and apply

- set relation with this quota[Date] with calendar data table

- add new running total measure for quota similar to what you did in sales 

drop this new Running total quota measure and you will able to compare it again running total of your sales.

I hope it is helpful, if you need further assistance, please post.

[Breckenridge]

That works great, thanks very much!  I was missing th {date..date} and expand - i didn't know i could do that as a query step, thanks!  Its ugly having a giant table of a thousand rows per sales person & a few hundred sales people, but i don't need to look at the table, just need to use it!