cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MAC Frequent Visitor
Frequent Visitor

Percentages

I am a Power BI newbie and am wondering how to best calculate a percentage of some values and then use the percentages in other calculations.

 

I currently have two data tables:  one for River Operating Hours (by vessel), and another for Total Monthly Operating Days (by vessel).  I'm looking to calculate the percent each river is of the vessel total and multiply that by Total Monthly Operating Days to arrive at River Operating Days.

 

I've included sample data below.....  Any assistance would be greatly appreciated!!!

 

Best,

MAC

 

Hours Table:    Oper Days Table  
Month_yearVessselRiverHrs Month_yearVessselDays
Jan-16JamesRed400 16-JanJames31
Jan-16JamesYellow100 16-JanPeter31
Jan-16JamesBlue125    
Jan-16JamesGreen119    
Jan-16PeterRed250    
Jan-16PeterYellow150    
Jan-16PeterBlue100    
Jan-16PeterGreen244    
        
        
        
River_Days: - Desired      
Month_yearVessselRiverOper_Days    
16-JanJamesRed16.66666667    
16-JanJamesYellow4.166666667    
16-JanJamesBlue5.208333333    
16-JanJamesGreen4.958333333    
16-JanPeterRed10.41666667    
16-JanPeterYellow6.25    
16-JanPeterBlue4.166666667    
16-JanPeterGreen10.16666667    
1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-qiuyu-msft
Moderator

Re: Percentages

Hi @MAC,

 

You need to create a calculate column to return Days value from the Oper Days Table in Hours Table:

 

Days = LOOKUPVALUE('Oper Days'[Days],'Oper Days'[Month_year],'Hours'[Month_year],'Oper Days'[Vesssel],'Hours'[Vesssel])

 

Then create a calculated column to return Oper_Days:

 

River Percent = SUM([Hrs])/CALCULATE(SUM([Hrs]),ALLEXCEPT(Hours,Hours[Vesssel],'Hours'[Month_year]))

 

q6.PNG

 

Please check attached .pbix.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Super User
Super User

Re: Percentages

I created a Measure like so:

 

River Percent = SUM([Hrs])/CALCULATE(SUM([Hrs]),ALLEXCEPT(Hours,Hours[Vesssel]))

And then a calculated column in Hours table like so:

 

Oper_Days = [River Percent]*CALCULATE(SUM('Oper Days'[Days]),RELATEDTABLE('Oper Days'))

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

Proud to be a Datanaut!


MAC Frequent Visitor
Frequent Visitor

Re: Percentages

Thank you!

 

Can I still use ALLEXCEPT if I have more than one layer of date data?

 

Hours Table:    Oper Days Table  
Month_yearVessselRiverHrs Month_yearVessselDays
Jan-16JamesRed400 Jan-16James31
Jan-16JamesYellow100 Jan-16Peter31
Jan-16JamesBlue125 Feb-16James29
Jan-16JamesGreen119    
Jan-16PeterRed250    
Jan-16PeterYellow150    
Jan-16PeterBlue100    
Jan-16PeterGreen244    
Feb-16JamesRed130    
Feb-16JamesYellow180    
Feb-16JamesBlue250    
Feb-16JamesGreen136    
        
        
River_Days: - Desired      
Month_yearVessselRiverOper_Days    
Jan-16JamesRed16.66666667    
Jan-16JamesYellow4.166666667    
Jan-16JamesBlue5.208333333    
Jan-16JamesGreen4.958333333    
Jan-16PeterRed10.41666667    
Jan-16PeterYellow6.25    
Jan-16PeterBlue4.166666667    
Jan-16PeterGreen10.16666667    
Feb-16JamesRed5.416666667    
Feb-16JamesYellow7.5    
Feb-16JamesBlue10.41666667    
Feb-16JamesGreen5.666666667    
Moderator v-qiuyu-msft
Moderator

Re: Percentages

Hi @MAC,

 

You need to create a calculate column to return Days value from the Oper Days Table in Hours Table:

 

Days = LOOKUPVALUE('Oper Days'[Days],'Oper Days'[Month_year],'Hours'[Month_year],'Oper Days'[Vesssel],'Hours'[Vesssel])

 

Then create a calculated column to return Oper_Days:

 

River Percent = SUM([Hrs])/CALCULATE(SUM([Hrs]),ALLEXCEPT(Hours,Hours[Vesssel],'Hours'[Month_year]))

 

q6.PNG

 

Please check attached .pbix.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MAC Frequent Visitor
Frequent Visitor

Re: Percentages

Thanks so much!!!!!