cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

A help needed with appending two tables to create a report

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!

Sample Data

 

Data Set:

  1. Actual Sales Report
  2. Sales Volume Forecast with historical data

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

  1. I would like to just show actual and forecast. (I want to show actual data from 2018/1/1 to 2018/5/1 and forecast data from 2018/6/1 to 2018/12/1. I do not want to show 2018/1/1-2018/5/1 data from the Table 2)
  2. For the forecast (From 2018/6/1 to 2018/12/1), based on volume forecasted, I would like to calculate Gross Sales to Net Sales based on the historical average by product level (in this case, Air Jordan 1 and Air Jordan 2)
  3. On the total row at the bottom of the screenshot, I would like to show the total numbers (Volume, Gross Sales, Net Sales %, etc.) on the items listed in the table. (In this case, 2018/1/1 - 2018/5/1 actual and 2018/6/1 - 2018/12/1 forecast)

 

 

Sample.PNG

 

I would like the final outcome to look like below screenshot and data.

Final Outcome

 

Final Output.png

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Highlighted
Community Support
Community Support

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"
    )
)

1.PNG

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)]
    )
)

2.PNG

 

And you can keep adding other columns

Step3:

You can drag new Sequence column into page level filter and filter blank

3.PNG

and use new column instead of the old column like below:

4.PNG

 

here is pbix, please try it.

https://www.dropbox.com/s/cjmxtz1m7j4c2t1/Net%20Sales%20Forecast%20Test.pbix?dl=0

 

Best Regards,

Lin

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted

@v-lili6-msft

 

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

Highlighted

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted

@v-lili6-msft

 

Thank you for your comment and sorry I missed your answers before.

Your solution works, but there is one thing.

 

I would like to

  1. For rebate, I have a specific plan on rebate percentage for particular month. If I want to use 2% rebate rate on gross sales on Q3, 3% rebate rate on Q4, and for Q2 use historical rate, how would the new calculated column look like?
  2. In forecast, we will have new products, which, of course will not have any historical data. For those, I have a calculated column with sales price. How should a new "Gross Sales" calculated column look like with new products?

Thank you for your support.

 

H

Highlighted

Can anyone please help me with above question on Oct 11th?

 

Your help is much appreciated.

 

Thanks,

 

H

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors