Forum Discussion

MAC's avatar
MAC
Frequent Visitor
9 years ago
Solved

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 Oper...
Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    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's avatar
MAC
Frequent Visitor
9 years ago

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    
v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
9 years ago

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's avatar
    MAC
    Frequent Visitor
    9 years ago

    Thanks so much!!!!!