Forum Discussion

SuperJK5088's avatar
SuperJK5088
Frequent Visitor
2 years ago
Solved

Monthly Quota Tracking with multiple criteria

Hi PBI Experts 🙂

 

I have the following tables and I need to calculate if the stores are meeting monthly quota. How do I do that? I also need to pick multiple months without messing up the baseline. Please help - thank you!

Need Help
  • DataNinja777's avatar
    DataNinja777
    2 years ago

    Hi SuperJK5088 ,

    You can achive your required output in the following manner. 

     

    1. Create the sales fact table by unpivoting the month columns using Power Query so that the actual sales by month will be in one column instead of 3 columns.  
    2. Create dimension tables for Product Type and Region and create relationships with Sales fact table and Monthly Quota fact tables. 
    3. Create calculate calendar table and create a relationship with the sales fact table date column.

    The resultant output is as shown below:

    I attach an example pbix file.

    Best regards,

2 Replies

  • DataNinja777's avatar
    DataNinja777
    Icon for Super User rankSuper User
    2 years ago

    Hi SuperJK5088 ,

    You can achive your required output in the following manner. 

     

    1. Create the sales fact table by unpivoting the month columns using Power Query so that the actual sales by month will be in one column instead of 3 columns.  
    2. Create dimension tables for Product Type and Region and create relationships with Sales fact table and Monthly Quota fact tables. 
    3. Create calculate calendar table and create a relationship with the sales fact table date column.

    The resultant output is as shown below:

    I attach an example pbix file.

    Best regards,

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    2 years ago

    what's the expected output based on the sample data you provided?