Percentages

[MAC]

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

[MAC]

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

[v-qiuyu-msft · Accepted solution]

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

Please check attached .pbix.

Best Regards,
Qiuyun Yu

[MAC]

Thanks so much!!!!!

[Greg_Deckler]

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