Create a new table using existing values

[Anonymous]

Hello,

I was hoping you could assist me with creating a table showing the following: 

- One column shows the amount of sales per date. So one row should be a date (for instance 01.01.21), and then I need the correspodning column to show the number of sales on that respective date. I already have data source showing many different dates, and also the amount of sales, but these do not correlate and therefore need assitance with.

Following this, I have a column with what the expected sales should be. Therefore, I wish to see how many sales were done on a particular date, then divide this number by what the expected sales was for that respective date, in order to gain this insight. 

Can you please help me in creating a table for this, or the best possible way to show the amount of sales done on a particular date then divide this output by expected sales?

[v-cazheng-msft]

Hi, @Anonymous 

Not very clear about your description. What is the meaning of ‘dates’ and ‘amount of sales’ in data source not correlate? Are ‘dates’ and ‘amount of sales’ two columns in different tables?

Does your expected table look like this?

date	amount of sales	expected sales	finished
2/1/2021	100	300	100/300
2/2/2021	200	450	200/450

Best Regards,

Caiyun Zheng

[BA_Pete]

Hi @Anonymous ,

Can you share a sample of your data please? This sounds like a relatively easy one to solve, but need to understand your source data structure etc.

Please remove any sensitive info before sharing if you are unable to share dummy data.

Also let me know what storage mode (Direct Query/Import) you are using on your data table.

Thanks,

Pete

[Anonymous]

Hi again, 

I am unable to provide sample data as all of this is sensitive information. Could you still try to help regardless, as you mentioned it me be a relatively easy fix?

I use the Import mode for this.

[BA_Pete]

@Anonymous ,

Ok, I'll try.

- Are all the columns that you mention (Date, Sales amount, Expected sales) in the same source table?

- When you say "amount of sales", do you mean the currency value or the unit quantity?

- Can you have multiple rows for the same date but with dfferent sales amounts e.g. different product lines or salesperson? Is this what you want grouped into a single date row with all the values summed or counted?

Pete