Aggregate to be used in the chart

[Anonymous]

Hi guys,

I have something very simple, but I cannot get the result. Below is the screenshot of the table. I need to calculate total # of hours to be used for my time series graph to be divided by the # of containers (this year)

Something like

count({<[TimeStamp.Calendar.Year] = {"$(=Max({1}[TimeStamp.Calendar.year]))"}>}CNTR_NUM)

/sum(aggr(((EST_DPTR_D-VSL_BERTH_D) *24)),VESSEL_VISIT_C )

So, for the example below it should be 14 / (7.2 + 6.2) = 1.0447761194029850746268656716418.

VESSEL_VISIT_C	EST_DPTR_D	VSL_BERTH_D	CNTR_NUM
1	2018-10-22 22:12:00	2018-10-22 15:00:00	1593364
1	2018-10-22 22:12:00	2018-10-22 15:00:00	1635264
1	2018-10-22 22:12:00	2018-10-22 15:00:00	1762778
1	2018-10-22 22:12:00	2018-10-22 15:00:00	6057661
1	2018-10-22 22:12:00	2018-10-22 15:00:00	APHU7202885
1	2018-10-22 22:12:00	2018-10-22 15:00:00	APZU2132290
1	2018-10-22 22:12:00	2018-10-22 15:00:00	APZU3457448
1	2018-10-22 22:12:00	2018-10-22 15:00:00	BMOU1102734
2	2018-10-22 21:12:00	2018-10-22 15:00:00	BMOU1288070
2	2018-10-22 21:12:00	2018-10-22 15:00:00	BMOU5899415
2	2018-10-22 21:12:00	2018-10-22 15:00:00	AMFU3206555
2	2018-10-22 21:12:00	2018-10-22 15:00:00	APHU6724720
2	2018-10-22 21:12:00	2018-10-22 15:00:00	APZU3190222
2	2018-10-22 21:12:00	2018-10-22 15:00:00	APZU3677870

Many thanks, as usual!

[sunny_talwar]

Try this (you had placed a parenthesis at the incorrect spot

Count({<[TimeStamp.Calendar.Year] = {"$(=Max({1}[TimeStamp.Calendar.year]))"}>} CNTR_NUM)/Sum(Aggr(((EST_DPTR_D-VSL_BERTH_D) *24), VESSEL_VISIT_C))

[Anonymous]

Hi Sunny,

As usual, thank you, for the help. It's good but it summed up ALL containers and DIVIDED by ALL time, instead of calculating it per month. Do I need to aggregate by Departure date as I am using Departure date for the y axis?

[sunny_talwar]

May be departure date in your Aggr() function

Count({<[TimeStamp.Calendar.Year] = {"$(=Max({1}[TimeStamp.Calendar.year]))"}>} CNTR_NUM)/Sum(Aggr(((EST_DPTR_D-VSL_BERTH_D) *24), VESSEL_VISIT_C, [Departure date]))

[Anonymous]

I guess you know what will come next - time out.

[sunny_talwar]

Would it be possible for you to share a sample app?

[Anonymous]

I need to set up a sample.

[sunny_talwar]

If you could do that, it would be great, because otherwise it is difficult to know what you have and what you want

[Anonymous]

Hi Sunny,

I have uploaded the file at app_sample - Google Drive.

Once you open, you will see that I used the 'container' field instead of COUNT NUM, and TimeStamp instead of Departure Date.

[sunny_talwar]

I won't be able to download anything from cloud, can you just attach it directly here

Uploading a Sample