Forum Discussion

PaulCr125's avatar
PaulCr125
Frequent Visitor
7 years ago
Solved

DAX Measure ?

Below is my example... In Excel it's easy. In Power BI... I'm struggling... User selects a begin date and end date. In the selection there is 22 work days between begin Date and end date. (Date calc...
Need Help
  • Ronald123's avatar
    Ronald123
    7 years ago

    Hello PaulCr125,

     

    Try this measure. 

     

     




     

     

     

     

     

     

    Greets,

     

    Ronald

     

PaulCr125's avatar
PaulCr125
Frequent Visitor
7 years ago

Thanks for the quick reply... I did not realize DIVIDE could be used that way... thanks again..

 

PaulCr125's avatar
PaulCr125
Frequent Visitor
7 years ago

Unfortunately it's still not returning the correct values.

it should be

60.00 Hrs/ (10 *7.2) = 104.17% InCorrect Should be 83.33%

67.50 Hrs/ (10 *7.2) = 104.17% InCorrect. Should Be 93.75%

34.00 Hrs/ (10 *7.2) = 118.06% InCorrect. Should Be 47.22%

105.40Hrs/ (10 *7.2) = 162.65% InCorrect. Should Be 146.39%

Calc_Col = FORMAT(DIVIDE(Sum(Qry_Machine_Utilization[Hours]),([DaysInPeriod] *7.2)),"0.00%")

 

 

 

 

  • Ronald123's avatar
    Ronald123
    Resolver III
    7 years ago

    PaulCr125,

     

    Try this, it's not a calculated colum but a measure.

    First > Hours: = SUM(Qry_Machine_Utilization[Hours])

     


    Second > Calc_Measure: = FORMAT(DIVIDE([Hours:];[DaysInPeriod:]*7,2);"0.00%")

     

    • PaulCr125's avatar
      PaulCr125
      Frequent Visitor
      7 years ago

      Ronald,

          Column "Hours": Native PowerBI (DAX) implicit sums based on Groupings.

          DAX_Hours: (Measure)  = Sum(Qry_Machine_Utilization[Hours])  They match, no problem there. 

          Calc_Col: (Measure) = FORMAT(DIVIDE(Sum(Qry_Machine_Utilization[Hours]),([DaysInPeriod] *7.2)),"0.00%")

          Calc_Col_II: (Measure) FORMAT(DIVIDE([DAX_Hours],[DaysInPeriod]*7,2),"0.00%")

          BUT... Line 1 answer I'm looking for is using 10 working days is 47.22% = 34/(10*7.2)

      Your screenshot from your last post is what I'm looking for. You put me on the correct path, when I do figure out the why, I'll make sure to follow up with you.

      Thanks for your time and effort.

      • Ronald123's avatar
        Ronald123
        Resolver III
        7 years ago

        Hi PaulCr125,

         

        The problem must be you measure [Daysinperiod].

        Put the measure in the matrix and check the value of every row.

         

        If that isn't problem, please upload a sample of your data.

         

        Greets,

         

        Ronald