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.

View solution in original post

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'))

I have book! Learn Power BI from Packt


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.

View solution in original post

MAC Frequent Visitor
Frequent Visitor

Re: Percentages

Thanks so much!!!!!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 58 members 1,235 guests
Please welcome our newest community members: