Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
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 |
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
Thanks so much!!!!!
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'))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |