Calculate Minimum Payment per Hour

Question

Dear all,&nbsp;I am trying to create a measure to do the following:&nbsp;I have some people that they have a minimum&nbsp;paid units of 5 Units/Hour. This means that if the do more, there is no payment and if they do&nbsp;less we only pay for the difference (they did 3 then we need to calculate the extra 2).&nbsp;I need to do a report to show how many extra units we paid per worker. The problem is that they can have more than one line per hour and that i need to sumarize the report per day and not per hour. I would also appreciate if is possible to summarize per different workers.&nbsp;I have a database with the following information:&nbsp;Worker IDDayReporting HourUnits122/08/2017151122/08/2017151222/08/2017153322/08/2017152422/08/2017155322/08/2017151522/08/2017158122/08/2017161222/08/2017163322/08/2017162&nbsp;I try to create a measure with an intermediary table with the filter per day and then apply If Sumx(intermediate table)&gt;3,3-Sumx(), but then i am a little bit lost on how to do it if i want to aggregate the full day.&nbsp;Another option will be to create a calculated column with the "extra payment" and then do a sum, but i do not know how to create a formula in each column to Sum the total units with the filter of hour, day and worker ID.&nbsp;I am quite new with the Power BI and i would appreciate if someone can give me some indications on how I need to approach it.&nbsp;Thanks,&nbsp;Francesc.

tommartens · Accepted Answer

Hey,&nbsp;I'm not sure if I totally understand your requirement, but here you'll find a little pbix file&nbsp;I created two calculated columns and one measureWorked Units - this columns sums the units per Worker | Day | HourWorked Units = 
CALCULATE(
	SUM('Table1'[Units])
	,ALLEXCEPT('Table1',Table1[Worker ID],'Table1'[Day],Table1[Reporting Hour])
) I changed the default aggregation to Average.&nbsp;The 2nd calculated column just calculates the extra payment. This column is hidden from the Report View&nbsp;The Measure "Extra Payment" calculates the measure that is applicable for each worker | day | hourExtra Payment = 
CALCULATE(
	SUMX(
	SUMMARIZE('Table1',Table1[Worker ID],Table1[Day],Table1[Reporting Hour],"ExtraPayement", AVERAGE(Table1[Extra Payments]))
	,[ExtraPayement])
)&nbsp;This measure&nbsp;aggregates the calculated column "Extra Payment" using the AVERAGE() function.This is inside the SUMX() so that Worker 1 has an Extra Payment of 7, 3 from working hour 15 and 4 from workig hour 16.&nbsp;Hope this is what you are looking forRegards

Forum Discussion

Calculate Minimum Payment per Hour

2 Replies

Related content

Mortgage Payment Calculation

Calculating the Minimum Value Across Multiple Months

First Minimum, Second Minimum and 3rd minimum value calculation

Dax calculate monthy and yearly rent payments

'Burn Down' of Payments due vs Payments Sent

Worker ID	Day	Reporting Hour	Units
1	22/08/2017	15	1
1	22/08/2017	15	1
2	22/08/2017	15	3
3	22/08/2017	15	2
4	22/08/2017	15	5
3	22/08/2017	15	1
5	22/08/2017	15	8
1	22/08/2017	16	1
2	22/08/2017	16	3
3	22/08/2017	16	2