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

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

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