Changes is in sales

Sales_Dataset.pbix (129.6 KB)

Hello,

I am confused on how to create a data model that will allow me to see the change in sales quarter over quarter.

I have a unique account table for each quarter(Q3 and Q4). There are some accounts in Q3 that are not in Q4 (due to cancellation). Likewise, there are accounts in Q4 not in Q3(due to new accounts).

I would like to set up this data model that I can see what accounts in Q4 are new and which ones have been canceled and how that has an impact on the sales in the company.

I also have a zone to region mapping and would like to be able to aggregate regional sales to Zone sales.

Thanks

@EncyclopediaBrown,

Welcome to the forum! (BTW - great username. I loved those books as a kid).

Ultimately, everything you’re looking to do are things Power BI does really well with pretty minimal effort, as long as your data model is properly structured.

The things that immediately jump out to me here are that:

  1. you definitely don’t want to split your sales table (i.e., fact table) by quarter. That is going to make your data model unnecessarily complicated and your DAX measures much more difficult.
  2. to get time intelligence analyses (such as Q over Q) to function properly, you will need to add a proper date table and mark it as such
  3. you’ll want to combine your multiple accounts tables into a single proper lookup table (i.e., dimension table )
  4. finally you’ll need to establish the proper relationships between your fact table, date table and other lookup tables

The place I would start is to go through the following course in detail, which will guide you through the necessary data transformation and data modeling concepts and procedures necessary to accomplish the steps above:

https://forum.enterprisedna.co/t/creating-and-modifying-a-date-table/3368

Then, once you’ve got your model set up correctly, these videos should help with the specific analyses you want to do:

https://forum.enterprisedna.co/t/calculate-difference-this-year-vs-last-year-in-power-bi-with-dax/506

See how this goes for you, and if you run into any problems or questions just give a shout out on the forum - tons of knowledgeable folks here eager to help.

  • Brian
2 Likes

Thanks for the feedback! I will take your suggestions and comeback if I get stuck.

Hi @EncyclopediaBrown, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!