Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Power BI Community,
I am struggling to append actual sales report with a forecast dataset to create one report (Actual + Forecast) on net sales,
if you could assist me with this, it will be much appreciated!
Data Set:
Objective:
I would like to append above two tables together as I did in the sample Power BI file (Please refer to below screenshot). and
I would like the final outcome to look like below screenshot and data.
The data set I have is rather big, so if I can somehow do all of the above with measure or calculated column
that will be a huge help.
Many thanks,
H
Solved! Go to Solution.
hi, @hidenseek9
Step2 is to calculate gross sales to net sales using historical data.
The logic is
(sum(historical gross sales of the same product name and the same product code and all is "actual" ) / sum(historical Volume (unit) of the same product name and the same product code and all is "actual" ) ) *Volume (unit) of current month
This is the formula that I refer to in your excel
and other columns like net sales are the same logic.
Best Regards,
Lin
hi, @hidenseek9
After my test about your pbix and excel files, you can do these as below:
Step1:
use EARLIER to add a new Sequence column
New Sequence = IF ( Append1[Sequence] = "Actual" && CALCULATE ( COUNT ( Append1[Sequence] ), FILTER ( Append1, Append1[Product Code] = EARLIER ( Append1[Product Code] ) && Append1[Product name] = EARLIER ( Append1[Product name] ) && Append1[Period] = EARLIER ( Append1[Period] ) ) ) > 1, "Actual", IF ( Append1[Sequence] = "Forecast" && CALCULATE ( COUNT ( Append1[Sequence] ), FILTER ( Append1, Append1[Product Code] = EARLIER ( Append1[Product Code] ) && Append1[Product name] = EARLIER ( Append1[Product name] ) && Append1[Period] = EARLIER ( Append1[Period] ) ) ) = 1, "Forecast" ) )
Step2:
use EARLIER to add a New Gross Sales (USD) column
New Gross Sales (USD) = IF ( Append1[New Sequence] = "Actual", Append1[Gross Sales (USD)], IF ( Append1[New Sequence] = "Forecast", ( CALCULATE ( SUM ( Append1[Gross Sales (USD)] ), FILTER ( Append1, Append1[Product Code] = EARLIER ( Append1[Product Code] ) && Append1[Product name] = EARLIER ( Append1[Product name] ) && Append1[New Sequence] = "Actual" ) ) / CALCULATE ( SUM ( Append1[Volume (unit)] ), FILTER ( Append1, Append1[Product Code] = EARLIER ( Append1[Product Code] ) && Append1[Product name] = EARLIER ( Append1[Product name] ) && Append1[New Sequence] = "Actual" ) ) ) * Append1[Volume (unit)] ) )
And you can keep adding other columns
Step3:
You can drag new Sequence column into page level filter and filter blank
and use new column instead of the old column like below:
here is pbix, please try it.
https://www.dropbox.com/s/cjmxtz1m7j4c2t1/Net%20Sales%20Forecast%20Test.pbix?dl=0
Best Regards,
Lin
Thank you for your reply.
How about my objective 2 on calculating gross sales to net sales using historical data?
If you could support on this, it will be much appreciated.
Many thanks,
H
hi, @hidenseek9
Step2 is to calculate gross sales to net sales using historical data.
The logic is
(sum(historical gross sales of the same product name and the same product code and all is "actual" ) / sum(historical Volume (unit) of the same product name and the same product code and all is "actual" ) ) *Volume (unit) of current month
This is the formula that I refer to in your excel
and other columns like net sales are the same logic.
Best Regards,
Lin
Thank you for your comment and sorry I missed your answers before.
Your solution works, but there is one thing.
I would like to
Thank you for your support.
H
Can anyone please help me with above question on Oct 11th?
Your help is much appreciated.
Thanks,
H
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |