Calculate Minimum Payment per Hour

[fturmo]

Dear all,

I am trying to create a measure to do the following:

I have some people that they have a minimum paid units of 5 Units/Hour. This means that if the do more, there is no payment and if they do less we only pay for the difference (they did 3 then we need to calculate the extra 2).

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.

I have a database with the following information:

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

I try to create a measure with an intermediary table with the filter per day and then apply If Sumx(intermediate table)>3,3-Sumx(), but then i am a little bit lost on how to do it if i want to aggregate the full day.

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.

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.

Thanks,

Francesc.

[TomMartens · Accepted solution]

Hey,

I'm not sure if I totally understand your requirement, but here you'll find a little pbix file

I created two calculated columns and one measure

Worked Units - this columns sums the units per Worker | Day | Hour

Worked Units = 
CALCULATE(
	SUM('Table1'[Units])
	,ALLEXCEPT('Table1',Table1[Worker ID],'Table1'[Day],Table1[Reporting Hour])
) 

I changed the default aggregation to Average.

The 2nd calculated column just calculates the extra payment. This column is hidden from the Report View

The Measure "Extra Payment" calculates the measure that is applicable for each worker | day | hour

Extra Payment = 
CALCULATE(
	SUMX(
	SUMMARIZE('Table1',Table1[Worker ID],Table1[Day],Table1[Reporting Hour],"ExtraPayement", AVERAGE(Table1[Extra Payments]))
	,[ExtraPayement])
)

 This measure 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.

Hope this is what you are looking for

Regards

[fturmo]

Thanks Tom for your suggestion it works perfectly.

Francesc.