cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jjuurikas Frequent Visitor
Frequent Visitor

Calculating from shifts data

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

1 REPLY 1
Community Support Team
Community Support Team

Re: Calculating from shifts data

@jjuurikas,

 

You may try using ALLSELECTED Function as a filter in CALCULATE Function to create the measure.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.