cancel
Showing results for
Did you mean:
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...

Community Support Team

## Re: Calculating from shifts data

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.

Announcements

#### Community Highlights

Find out what's new in the Power BI Community!

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 312 members 3,292 guests
Recent signins: