Annual percentage split on different items and split value accordingly

[norbi]

Hi,

I would truly appreciate some help with the following challenge. I have a total val for a year and the percentage split over the period. I would like to combine these where Table_1 would have the total val for the different items split according to the months percentages in Table_2. Then the output would look something like Table_3.

Table_1

Item	Val
216	150000
250	215000
315	175000
…

Table_2

Month	%_Trend
2016-01	7.02%
2016-02	6.90%
2016-03	7.43%
2016-04	7.75%
2016-05	7.66%
2016-06	7.83%
2016-07	8.46%
2016-08	9.04%
2016-09	10.47%
2016-10	10.56%
2016-11	7.90%
2016-12	8.99%

Table_3

Period	Item	Val
2016-01	216	10531.59
2016-02	216	10355.36
2016-03	216	11144.49
2016-04	216	11618.87
2016-05	216	11484.85
2016-06	216	11739.28
2016-07	216	12688.6
2016-08	216	13566.33
2016-09	216	15699.6
2016-10	216	15833.32
2016-11	216	11852.78
2016-12	216	13484.94
2016-01	250	15095.28
2016-02	250	14842.68
2016-03	250	15973.76
2016-04	250	16653.72
2016-05	250	16461.61
2016-06	250	16826.3
2016-07	250	18187
2016-08	250	19445.08
2016-09	250	22502.76
2016-10	250	22694.42
2016-11	250	16988.98
2016-12	250	19328.41
2016-01	315	12286.85
2016-02	315	12081.25
2016-03	315	13001.9
2016-04	315	13555.35
2016-05	315	13398.99
2016-06	315	13695.83
2016-07	315	14803.37
2016-08	315	15827.39
2016-09	315	18316.2
2016-10	315	18472.21
2016-11	315	13828.24
2016-12	315	15732.43
….

Any assistance would be truly appreciated.

[Vvelarde]

@norbi

Hi:

Try with;

Modeling - New Table

Cruce = CROSSJOIN(Table1,Table2)

After that created a calculated column with 

NVal = Cruce[Val]*Cruce[%_Trend]

Regards

Victor

[norbi]

Hi @Vvelarde,

I am a bit new to Power BI, could you kindly elaborate or provide a sample Power BI file?

[Vvelarde]

@norbi

Please review the PBIX

Here

Let me know if works for your question

Regards

Victor

Lima - Peru

[norbi]

Hi @Vvelarde,

No the file does not work and does not open?

[Vvelarde]

@norbi

Try Downloading and Opening with PBI Desktop November Update.

Regards

[norbi]

Hi @Vvelarde,

Thank you for the file, it works on the data thank you. Would it be possible to add something like this to a measure instead of a table? Reason for this is that my actual data has many fields and currently getting and error “The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.”?

[Ashish_Mathur]

Hi,

I have solved this problem using Query Editor.  Download the file from here.

[norbi]

Hi @Ashish_Mathur,

Whn looking at the attached document, the two tables are only in two tables, however the % split for the months is not present?

[Ashish_Mathur]

Hi @norbi,

Table_1 has the final answer.  Table_2 is where you will see the monthly split.