Forum Discussion
Helper IBuild a model to extrapolate sales volume
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!
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 + __extrapolationAnd you will get this tableIf this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
6 Replies
- mahoneypat
Microsoft Employee
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 + __extrapolationAnd you will get this tableIf this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
kuksziHi 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