cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

daily sales v monthly target by sales team

Hi All

 

Im a newbie at powerbi and trying to hash together a sales report for my employer. Im specifically stuck on getting the sales (which is a list of daily activity) to compare against a monthly target for different teams.

It keeps bringing in the monthly target as a straight line across the months when put into a column and line graph (when it actually varies month to month)

Source of data is excel.

 

Tables:

 

Date table – used DAX to create every single date between 2018/04/01 and 2022/03/31 (Date = CALENDAR(date(2018,04,01),date(2022,03,31))

Also used DAX to create a month column in the same table (Month = format('Date'[Date],"mmm-yy")) ,  but that repeats for every single day (so its not a unique entry when trying to create a 1 to many table relation on the month)

 

Sales table – contains date (linked to date table), territory, product type, qty of product, price per product, order number, freight, discount, total sales value for order etc This has a row for every transaction every day so nothing is unique.

 

Product Types - list of unique product types which I link to sales table (product type field) so I can report on product groupings

 

Territory – list of unique territory names which I link to sales table (territory field)

 

Territory Target –contains a column for territory (there is about 6 territories), month, target, and a cumulative target

Eg

Team1,  apr-18, 10,000, 10,000

Team1, may-18, 12,000, 22,000

Team2, apr-18, 8,000, 8,000

Team2 may-18, 9,000, 17,000

Etc..

So territory and month are not unique as they repeat for each month of the year.

 

So how do I link the daily sales ,to monthly target? Neither has a 1 to many relation (always get the many to many and the issues that causes)

 

Should I separate this out further in excel before bringing into powerbi?

In excel I can formulate the monthly sales totals in the same table as territory target, to then allow this to pull together in powerbi, but it would be nice to understand how this can be done in powerbi without doing it all in excel.

 

Many thanks in advance– and please explain to the greatest detail possible, as I said Im a newbie and using google to find my way around all this.

 

Thanks

Kev

1 ACCEPTED SOLUTION
Super User III
Super User III

Your link from Date table to Sales table should be on the date, and it should be 1:M.

 

In your Target table create another column with an artificial date for each month (for example first day of the month). Link that to the Date table, should be 1:1 but you can change the filter direction to be single way from Date to Target.

View solution in original post

1 REPLY 1
Super User III
Super User III

Your link from Date table to Sales table should be on the date, and it should be 1:M.

 

In your Target table create another column with an artificial date for each month (for example first day of the month). Link that to the Date table, should be 1:1 but you can change the filter direction to be single way from Date to Target.

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session Drive Data Culture with Power BI: Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors