DAX Measure ?

[PaulCr125]

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 calculation is not an issue) I need to apply the formula to the calc col in a Power BI table report. 

In Power BI there is already a Sum(Hours) Grouped By name. I need to apply the constants to the formula after the sums are calculated, in theory just like the Excel sheet.

[Ronald123 · Accepted solution]

Hello @PaulCr125,

Try this measure. 

Greets,

Ronald

[PaulCr125]

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

[PaulCr125]

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]

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

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]

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

[PaulCr125]

That's the problem.... the days in period needs to say constant...

If I hard code the 10 into the Calc_Col_II = FORMAT(DIVIDE([DAX_Hours],10*7,2),"0.00%") , it works fine.

I need the DaysInPeriod Measure to equal the the date difference in the slider begin date and end date and stays constant. 

[Ronald123]

Hello @PaulCr125,

Create a date table;

DateTable=CALENDAR (MINX (Date, [TicketDate]), MAXX (Date, [TicketDate]))

Make a connection between Date[TicketDate] >DateTable[Date]

Then drag the date colum from the date table in the slicer.

At last, make you're [DaysInPeriod] measure with the dates from the DateTable.

Greets,

Ronald

[PaulCr125]

Ronald123,

    That did it... I also understand the theory, and was not aware of the usage of MAXX. I appreciate your patience to bring me through the interative steps to actully understand my solution. Many questioners want it done for them... that returns nothing in knowledge. I will take the knowledge forward, thanks again for your help.

Paul