Hey all!
I've run into a problem where I need to calculate the averages of summation of data and then add/substract/compare them with selected numbers or numbers that I have already calculated with measures.
Into the details:
Let's say I have a shift where the columns that run straight from the database (some calculated extra in Query) are:
Worker | WorkerLocation | WorkerName | ShiftEndDate | ShiftDuration(h) | ShiftProdSum(€) |
1 | Tartu | Peter | February 23 | 5,12 | 44,20 |
2 | Tallinn | John | February 22 | 6,8 | 67,10|
1 | Tartu | Peter | February 1 | 11,31 | 69,30|
2 | Tallinn | John | February 21 | 11,67 | 72,70|
[..]
Now I went to the visualization part and aggregated the data ( by just choosing "SUM" on the ShiftProdSum) and have the worker data summed.
Worker | WorkerLocation | WorkerName | (Latest)ShiftEndDate | ShiftDuration(h) | ShiftProdSum(€) |
1 | Tartu | Peter | February 23| 16,43 | 113,5 |
2 | Tallinn | John | February 22| 18,49 | 139,8 |
[...]
Since I needed to get the "€ per hour" for each shift, I managed to do a measure that works for me.
€/h Total = DIVIDE(SUM([ShiftProdSum]), SUM([ShiftDuration]), "wtf")
Worker | WorkerLocation | WorkerName | (Latest)ShiftEndDate | ShiftDuration(h) | ShiftProdSum(€) | €/h Total
1 | Tartu | Peter | February 23| 16,43 | 113,5 | 6,91
2 | Tallinn | John | February 22| 18,49 | 139,8 | 7,56
[...]
Where I struggle now, is how to do calculations with that. I would need to Substract the total average €/h of shifts per time chosen (basically the same measure by time above but without being on specific columns) with the €/h of each sum of shifts
If my measure says the total average of location TARTU is 7,50
And if my measure says the total average of location TALLINN is 7,20
Then I would like my next column there to calculate the 7,50 -6,91 = 0,59
Worker | WorkerLocation | WorkerName | (Latest)ShiftEndDate | ShiftDuration(h) | ShiftProdSum(€) | €/h Total | Difference
1 | Tartu | Peter | February 23| 16,43 | 113,5 | 6,91 | 0,59
2 | Tallinn | John | February 22| 18,49 | 139,8 | 7,56 | -0,36
[...]
I cannot for the life of me figure out how to get the last result. Any ideas? I will be calculating more with the last...
