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!

[Anonymous]

Done, I have updated the initial post. In the formula I used container instead of CNT_NUM and TimeStamp instead of Departure Date.

[sunny_talwar]

Based on the data provided... what is the output you are hoping to see?

[Anonymous]

Sunny, thank you for the response. I calculated it in xls: # of hrs is the sum of (Timestamp - Berth ) * 24 for all vessels. # of cont is the number of containers. metric is # of hrs / # of containers. The graph is the graphical image of the metric.

[sunny_talwar]

Try this

Round(count({<type={'vessel'}>}container)/Sum(Aggr(((Only({$<type={'vessel'}>}TimeStamp)-VSL_BERTH_D)*24), visit, [TimeStamp.Calendar.Month])))

[Anonymous]

Sunny, Sunny, I don't know how you do it. It worked BUT if I select a date from the calendar, the graph will react on the selection and show the value as per the selected date, although it's supposed to stay static.

I also added the condition to show just the current year:

Round(count({<type={'vessel'}, [TimeStamp.Calendar.Year] = {"$(=Max({1}[TimeStamp.Calendar.year]))"}>}container)

/

Sum(Aggr(((Only({$<type={'vessel'}, [TimeStamp.Calendar.Year] = {"$(=Max({1}[TimeStamp.Calendar.year]))"}>}TimeStamp)-VSL_BERTH_D)*24), visit, [TimeStamp.Calendar.Month])))

Another question, your logic doesn't seem to work if I use it to show the trend line for the previous year. Is it because TimeStamp.Calendar.Month?

Round(count({<type={'vessel'}, [TimeStamp.Calendar.Year] = {"$(=Max({1}[TimeStamp.Calendar.year])-1)"}>}container)

/

Sum(Aggr(((Only({$<type={'vessel'}, [TimeStamp.Calendar.Year] = {"$(=Max({1}[TimeStamp.Calendar.year])-1)"}>}TimeStamp)-VSL_BERTH_D)*24), visit, [TimeStamp.Calendar.Month]

)))

[sunny_talwar · Accepted solution]

To keep it static... try this

Round(count({1<type={'vessel'}>}container)/Sum({1}Aggr(((Only({1<type={'vessel'}>}TimeStamp)-Only({1} VSL_BERTH_D))*24), visit, [TimeStamp.Calendar.Month])))

[sunny_talwar]

For previous year... may be add Year to the Aggr() dimension also

Round(count({<type={'vessel'}, [TimeStamp.Calendar.Year] = {"$(=Max({1}[TimeStamp.Calendar.year])-1)"}>}container)

/

Sum(Aggr(((Only({$<type={'vessel'}, [TimeStamp.Calendar.Year] = {"$(=Max({1}[TimeStamp.Calendar.year])-1)"}>}TimeStamp)-VSL_BERTH_D)*24), visit, [TimeStamp.Calendar.Month], [TimeStamp.Calendar.Year]

)))

[Anonymous]

It works perfectly! Slightly changed to

Round(count({1<type={'vessel'}, [TimeStamp.Calendar.Year] = {"$(=Max({1}[TimeStamp.Calendar.year]))"}>}container)/

Sum({1}Aggr(((Only({1<type={'vessel'}, [TimeStamp.Calendar.Year] = {"$(=Max({1}[TimeStamp.Calendar.year]))"}>}TimeStamp)-Only({1} VSL_BERTH_D))*24), visit, [TimeStamp.Calendar.Month])))

to get this year values only

Sunny, just two more questions:

1. I look at the options I tried, and there was one similar to yours, but I did not have TimeSTamp.Calendar.Month. With this part, you are aggregating by month, are not you?

2. So, when I need to fix, I need basically to 1s whatever I only can - I see that you used '1' in almost all the fields, don't I?

[Anonymous]

Nope, it did not work i.e. the previous year line did not even show up.

I also tried but it did not work

Round(count({1<type={'vessel'},[TimeStamp.Calendar.Year] = {"$(=Max({1}[TimeStamp.Calendar.year])-1)"}>}container)

/

Sum({1}Aggr(((Only({1<type={'vessel'},[TimeStamp.Calendar.Year] = {"$(=Max({1}[TimeStamp.Calendar.year])-1)"}>}TimeStamp)

-

Only({1<[TimeStamp.Calendar.Year] = {"$(=Max({1}[TimeStamp.Calendar.year])-1)"}>} VSL_BERTH_D))*24),

visit,[TimeStamp.Calendar.Month] ,[TimeStamp.Calendar.Year])))

[sunny_talwar]

1) Yes, aggregating by Month and Visit, both

2) Yes... if you don't want your expression to change based on any selection... you need 1 in all places which have aggregation.... sometimes you don't have an aggregation, but in order to ignore selection you need to add one... like we did for VSL_BERTH_D.