Problems with pivot and calcuated fields!

[hakanfa]

Hi All,

I have a major problem with Pivot – here a short description. I’m trying to calculate how many units per departure we have on a ferry. The raw data looks like following:

Date	Month	Time	Product	Length	Amount	Departures
23.3.2020	March	18:30	CAR	6	2	1
23.3.2020	March	18:30	VAN	8	2	1
23.3.2020	March	18:30	CARAVAN	12	4	1
23.3.2020	March	20:30	CAR	6	20	1
23.3.2020	March	20:30	VAN	8	8	1

Now if I make a calculated field for units per departure I get for the 18:30 8 units on 3 departures – but the correct value would be 8 units on 1 departure, and for the 20:30 I get 38 units on 2 departures when the correct answer would be 28 units on 1 departure. How could I make the pivot table understand the logic Im looking for?


All possible help and thoughts are appreciated!
Hakan

 

[GraH]

Hihakanfa,

A pivot will see the rows of your raw data as single records. And thus count 3 departures whereas you know it is one.
Either upgrade to "PowerPivot" useing DAX measures or easier add an extra column in your raw data that forces the unique count.

Something like

 

[hakanfa]

Hihakanfa,

A pivot will see the rows of your raw data as single records. And thus count 3 departures whereas you know it is one.
Either upgrade to "PowerPivot" useing DAX measures or easier add an extra column in your raw data that forces the unique count.

Something like

View attachment 9786

Thank you for valuable comments - I solved this by swapping to PowerPivot an adding a "calculated measure". First I added an extra column with an unique value for the departures (DepID). Then I created a "calculated measure" = SUM(Meters)/DISTINCTCOUNT(DepID) works like a charm :D

 

[GraH]

Nicely done.