Build a model to extrapolate sales volume

[kukszi]

Hi there,

I need to build a model in Power BI which extrapolates sales numbers, but I don't know how to start. The concept is the following: I have sales volume for Ontario and BC, and would like to extrapolate in order to get the total Canada sales.

The data I have is the following:

Actuals:

Customer	Province	Product	Volume
Cust 1	ON	Prod 1	100
Cust 1	ON	Prod 2	200
Cust 1	BC	Prod 1	80
Cust 1	BC	Prod 2	170
Cust 2	ON	Prod 1	500
Cust 2	ON	Prod 2	350
Cust 2	BC	Prod 1	200
Cust 2	BC	Prod 2	300

Extrapolation parameters:

Customer	In Province	Base to be used	Extrapolation factor
Cust 1	AB	ON	70%
Cust 1	SK	ON	40%
Cust 2	AB	BC	110%
Cust 2	SK	BC	60%

Expected outcome:
Cust 1:
ON Prod 1 = 100
ON Prod 2 = 200
BC Prod 1 = 80
BC Prod 2 = 170
AB Prod 1 = 100 x 70% = 70
AB Prod 2 = 200 x 70% = 140
SK Prod 1 = 100 x 40% = 40
SK Prod 2 = 200 x 40% = 80
Total National Sales to Cust 1 = 880

Cust 2:
ON Prod 1 = 500
ON Prod 2 = 350
BC Prod 1 = 200
BC Prod 2 = 300
AB Prod 1 = 200x 110% = 220
AB Prod 2 = 300x 110% = 330
SK Prod 1 = 200x 60% = 120
SK Prod 2 = 300x 60% = 180
Total National Sales to Cust 2 = 2200

Total National Sales to Cust 1 and Cust 2 = 3080

What is the best way to build a calculation like the above explained?

Thanks in advance!

[mahoneypat · Accepted solution]

Here is one way to do it.

1. Make two DAX tables for Customers and Provinces with these two expressions

Customers = DISTINCT(UNION(VALUES(Actuals[Customer ]), VALUES(Extrapolate[Customer ])))

Provinces = DISTINCT(UNION(VALUES(Actuals[Province ]), VALUES(Extrapolate[In Province ])))

2.  Make relationships from these two tables to each of your original tables (in Extrapolate table, the relationship is to the [In Province] column.

3.  Make a table visual with Customer (from new table), Province (from new table) and Product (from Actuals table), along with this measure:

Extrapolated Total =
VAR __actuals =
SUM ( Actuals[Volume ] )
VAR __extrapolation =
SUMX (
Extrapolate,
Extrapolate[Extrapolation factor]
* CALCULATE (
SUM ( Actuals[Volume ] ),
ALLEXCEPT ( Actuals, Actuals[Product ] ),
TREATAS ( VALUES ( Extrapolate[Base to be used ] ), Actuals[Province ] ),
TREATAS ( VALUES ( Extrapolate[Customer ] ), Actuals[Customer ] )
)
)
RETURN
__actuals + __extrapolation

And you will get this table

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[kukszi]

Hi Pat,
This is great, thank you very much. The values in the "Extrapolated Total" column are calculated properly, but when I'm summarizing the individual rows, the result is 3,080 and not 7,220. How can I fix the totals?
Thanks,
Kukszi

[mahoneypat]

Not sure what you mean.  Are you using the measure in a matrix visual?  What is the field for columns?  In any case, total problems are often solved with this kind of an approach:

IteratorMeasure = SUMX(VALUES(Table[RowOrColumn in Visual]), [Extrapolated Total])

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[kukszi]

I'm using table visual as you suggested, which looks like this:

Also, tried the IteratorMeasure, which gave a completely different result.

When I'm adding up manually the values in Extrapolated Totals column, I get 3080 and not 7220. Am I doing something wrong?

[v-yiruan-msft]

Hi @kukszi ,

Please try to update the formula of measure "IteratorMeasure" as below:

IteratorMeasure= SUMX(VALUES('Customers'[Customer]),SUMX(VALUES('Provinces'[Province]), SUMX(VALUES('Actuals'[Product]),[Extrapolated Total])))

Best Regards

Rena

[kukszi]

Thank you Rena, it worked!