Forum Discussion
Urgent Help Required : Profits vs Sales Targets set up
Hi,
I have tried researching (new to power BI) and trying to understand how to approach and show monthly sales figures vs monthly targets and I dont seem to understand the logic at all. Im not sure I have understood how to even go about it. Need your help to understand what needs to be done.
1. I have raw data that provides the daily jobs executed and their respective estimated profit. Every Job comes under a certain department as shown below.
2. A target has been given to me for every department for every month. For example, target for Transportation department for January is 220,000 and for February it is 298,000 etc. I manually created this table with hardcoded values including putting Date column. I also created a column, Target_month = MONTH(Target[Date]) because I thought I would need the month.
3. I tried setting a relationship between these two tables and it said Many-Many relation ship which is right as the departments appear multiple times in each table. Im not sure if this step what I am doing is right or not.
4. I tried creating a visual to show the monthly target along with filter for department and it shows up fine as below.
5. When I include the estimated profit data in the table visual it shows me as below. It adds up the the profit numbers for January and February (because all those two months are available as of now) and shows together that for every month (45,406.57). I am not sure how do I show the total numbers for january and only february seperately in the table. The card visuals to show total profit for January seperatley and february for clarity. Also, as the future months (march, April etc) are still not there i dont them to be shown. As the current month is only february so only february data needs to be shown. I have just made a table in excel to show how I would like it to look.
Result required: As the next months data keeps coming up the visual would ideally be updated with the updated numbers for future months. It would be great if I could also show like a trend visual for a department on profits vs targets by month
I have also tried creating individual measures hardcode the targets to see if that woudl worl but I could see that made no sense.
I hope i have been able to clearly explain the issue I am facing and what I am trying to achieve. Any help would be appreciated Thanks.
Hi arshad11
In your model, I would suggest adding two Dim tables Date and Department. In Dim Date table, you have continuous distinct dates in Date column. In Dim Department table, you have all distinct departments. Then create the following relationships between tables:
- Date Table (Date) - Main Table (Job_Date): one-to-many with single direction;
- Date Table (Date) - Target Table (Target_Date): one-to-many with single direction;
- Department Table (Department) - Main Table (Department): one-to-many with single direction;
- Department Table (Department) - Target Table (Department): one-to-many with single direction;
Then use Department table's Department column in the slicer to filter other tables. And use Date table's Month column to filter profits and targets in a table visual.
Model:
Understand star schema and the importance for Power BI - Power BI | Microsoft Docs
Date table:
https://radacad.com/do-you-need-a-date-dimension
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Regards,
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.v-jingzhang thank you so much for your help. I followed every step you gave and it worked perfectly. Really appreciate your help and the community to provide a such an amazing platform for help 🙂
Really appreciate v-jingzhang !!THank you
6 Replies
v-jingzhangCommunity Support
Hi arshad11
In your model, I would suggest adding two Dim tables Date and Department. In Dim Date table, you have continuous distinct dates in Date column. In Dim Department table, you have all distinct departments. Then create the following relationships between tables:
- Date Table (Date) - Main Table (Job_Date): one-to-many with single direction;
- Date Table (Date) - Target Table (Target_Date): one-to-many with single direction;
- Department Table (Department) - Main Table (Department): one-to-many with single direction;
- Department Table (Department) - Target Table (Department): one-to-many with single direction;
Then use Department table's Department column in the slicer to filter other tables. And use Date table's Month column to filter profits and targets in a table visual.
Model:
Understand star schema and the importance for Power BI - Power BI | Microsoft Docs
Date table:
https://radacad.com/do-you-need-a-date-dimension
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Regards,
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.v-jingzhang thank you so much for your help. I followed every step you gave and it worked perfectly. Really appreciate your help and the community to provide a such an amazing platform for help 🙂
Really appreciate v-jingzhang !!THank you
- v-jingzhang
Thanks for your kind words. Keep on bringing more questions and also share your experience to help others in the community!
- Syndicate_Admin
Administrator
@v-jingzhang thank you very much for your help. I followed every step you took and it worked perfectly. We really appreciate your help and community to provide an amazing platform to help 🙂
Really appreciate @v-jingzhang !! Thank you
Hi Amit,
I did find your post, ( https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-Convert-to/ba-p/1476400) and it was very well explained. I feel i understand it but Im not sure I know how to use it to my issue. Can you please have a look into this please.