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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Need help in finding ratio per month and plot the month wise trend.

I have two tables, 1st table which gives month wise waste weight for the range of products and 2nd table contains the tonnage produced per month. I would like to find the % waste per month against the tonnage produced and plot the trend month wise.

For example:

Total waste weight in Jan 2021 is 5624kg & Tonnage produced in Jan 1386000 .

% waste for Jan=  5.624 tons / 1386 tons = 0.40%, Like this need for each month and plot the trend month wise.

 

I am new to powerbi, support will help me.

 

Table1 waste weight    
 ProductMonthYearweight
 BlackmixJan20213158
 labscrapJan20211104
 carbonblackJan2021862
 GpmixJan2021170
 ElastomerJan20210
 BlocsJan202118
 BUJan202173
 ChemicalJan20210
 silicaJan2021239
 BlackmixFeb20212552.4
 labscrapFeb20211303.6
 carbonblackFeb20211249
 GpmixFeb20211395
 ElastomerFeb2021109
 BlocsFeb202114
 BUFeb202155
 ChemicalFeb20218
 silicaFeb20214
 BlackmixMar20211832.5
 labscrapMar20211216.5
 carbonblackMar20211547
 GpmixMar20211126
 ElastomerMar2021704
 BlocsMar2021237
 BUMar2021115
 ChemicalMar2021286
 silicaMar20217
 BlackmixApr20213390.4
 labscrapApr20211311.5
 carbonblackApr20211224
 GpmixApr2021414
 ElastomerApr2021199
 BlocsApr20219
 BUApr2021111
 ChemicalApr202126
 silicaApr202118
Table 2 tonnage 
   
 MonthWeight
 Jan1386000
 Feb1726000
 Mar2079000
 apr2205000
 may1436000
 Jun1637000
 Jul1760000
 Aug2200000

 

2 ACCEPTED SOLUTIONS

Hi @Anonymous 

 

Please change the Cross filter Direction from Single to Both [relation between those 2 tables]and check the chart again.

VahidDM_1-1629556853079.png

VahidDM_2-1629556885794.png

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos VahidDM_3-1629556908488.png !!

 

 

 

 

View solution in original post

V-pazhen-msft
Community Support
Community Support

@Anonymous 

 

According to this function:

Measure =

DIVIDE(SUM('Table1'[weight]),SUM('Table2'[Weight]))

 

In the clustered column chart, do you use the column [Month] in Table1 instead of the column [Month] in Table2. Using the column [Month] of different tables will produce different results:

Vpazhenmsft_1-1629876819194.png

 

 

Best Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

View solution in original post

8 REPLIES 8
V-pazhen-msft
Community Support
Community Support

@Anonymous 

 

According to this function:

Measure =

DIVIDE(SUM('Table1'[weight]),SUM('Table2'[Weight]))

 

In the clustered column chart, do you use the column [Month] in Table1 instead of the column [Month] in Table2. Using the column [Month] of different tables will produce different results:

Vpazhenmsft_1-1629876819194.png

 

 

Best Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Anonymous
Not applicable

@VahidDM Hi..

 

I got the table now.

Nagarajan_0-1629555878277.png

But when i put that in clustered coloumn chart for the month wise trend. the value changes.

Any mistake i am making. Kindly guide.

 

Nagarajan_1-1629555953873.png

 

 

 

hi @Anonymous 

 

hqve you changed the Cross filter Direction from Single to Both [relation between those 2 tables] and check the chart again.

 

VahidDM_0-1629879798954.png

 

VahidDM_1-1629879799005.png

 

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos !!

Hi @Anonymous 

 

Please change the Cross filter Direction from Single to Both [relation between those 2 tables]and check the chart again.

VahidDM_1-1629556853079.png

VahidDM_2-1629556885794.png

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos VahidDM_3-1629556908488.png !!

 

 

 

 

VahidDM
Super User
Super User

Hi @Anonymous 

 

Make sure your there is a relation between your tables:

VahidDM_0-1629535516252.png

Then try this measure:

% waste =
SUM ( Table1[weight] ) / SUM ( 'Table 2'[Weight] )

 

Use this measure and Month column to create a table like this:

VahidDM_1-1629535637468.pngVahidDM_2-1629535651922.png

Download this file for your reference: https://gofile.io/d/3NcsBq

 

 

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos VahidDM_3-1629535771967.png !!

Anonymous
Not applicable

Hi, Thanks for your help.

I tried creating a table, whereas the % waste ( %IQP  ) is displaying 0.

am i making any mistake ?

Nagarajan_0-1629540482755.png

 

Hi @Anonymous 

 

Change the type of %IQP value to the percentage.

 

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos VahidDM_0-1629542458910.png !!

Anonymous
Not applicable

and also pls help me to make a Month wise %Waste trend chart.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

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