Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
Hi:
Try with;
Modeling - New Table
Cruce = CROSSJOIN(Table1,Table2)
After that created a calculated column with
NVal = Cruce[Val]*Cruce[%_Trend]
Regards
Victor
Hi @Vvelarde,
I am a bit new to Power BI, could you kindly elaborate or provide a sample Power BI file?
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.”?
Hi,
I have solved this problem using Query Editor. Download the file from here.
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?
Hi @norbi,
Table_1 has the final answer. Table_2 is where you will see the monthly split.
User | Count |
---|---|
123 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
183 | |
90 | |
67 | |
62 | |
53 |