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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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