Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
norbi
Helper I
Helper I

Annual percentage split on different items and split value accordingly

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.

9 REPLIES 9
Vvelarde
Community Champion
Community Champion

@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




Lima - Peru

Hi @Vvelarde,

 

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

Vvelarde
Community Champion
Community Champion

@norbi

 

Please review the PBIX

Here

 

Let me know if works for your question

 

Regards

 

Victor

Lima - Peru




Lima - Peru

Hi @Vvelarde,

 

No the file does not work and does not open?

Vvelarde
Community Champion
Community Champion

@norbi

 

Try Downloading and Opening with PBI Desktop November Update.

 

Regards

 

 

 




Lima - Peru

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.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.