cancel
Showing results for
Did you mean:
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_year Vesssel River Hrs Month_year Vesssel Days Jan-16 James Red 400 16-Jan James 31 Jan-16 James Yellow 100 16-Jan Peter 31 Jan-16 James Blue 125 Jan-16 James Green 119 Jan-16 Peter Red 250 Jan-16 Peter Yellow 150 Jan-16 Peter Blue 100 Jan-16 Peter Green 244 River_Days: - Desired Month_year Vesssel River Oper_Days 16-Jan James Red 16.66666667 16-Jan James Yellow 4.166666667 16-Jan James Blue 5.208333333 16-Jan James Green 4.958333333 16-Jan Peter Red 10.41666667 16-Jan Peter Yellow 6.25 16-Jan Peter Blue 4.166666667 16-Jan Peter Green 10.16666667
1 ACCEPTED SOLUTION

Accepted Solutions
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]))

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

## 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

Proud to be a Datanaut!

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_year Vesssel River Hrs Month_year Vesssel Days Jan-16 James Red 400 Jan-16 James 31 Jan-16 James Yellow 100 Jan-16 Peter 31 Jan-16 James Blue 125 Feb-16 James 29 Jan-16 James Green 119 Jan-16 Peter Red 250 Jan-16 Peter Yellow 150 Jan-16 Peter Blue 100 Jan-16 Peter Green 244 Feb-16 James Red 130 Feb-16 James Yellow 180 Feb-16 James Blue 250 Feb-16 James Green 136 River_Days: - Desired Month_year Vesssel River Oper_Days Jan-16 James Red 16.66666667 Jan-16 James Yellow 4.166666667 Jan-16 James Blue 5.208333333 Jan-16 James Green 4.958333333 Jan-16 Peter Red 10.41666667 Jan-16 Peter Yellow 6.25 Jan-16 Peter Blue 4.166666667 Jan-16 Peter Green 10.16666667 Feb-16 James Red 5.416666667 Feb-16 James Yellow 7.5 Feb-16 James Blue 10.41666667 Feb-16 James Green 5.666666667
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]))

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.
Frequent Visitor

## Re: Percentages

Thanks so much!!!!!

Announcements

#### Challenge: Can You Solve These?

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

#### Community News & Announcements

Get your latest community news and announcements.

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

#### Win Power BI Swag with Community Kudopalooza!

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

Top Kudoed Authors
Users Online
Currently online: 58 members 1,235 guests
Recent signins: