Is there a way to use multi-table data in CrossTable together with Sum aggregation? Sample data looks like this:
UniqueCount(actid) aggregation works perfect with joined data:
But I have to calculate Sum of [bdg] column for each cell, and value for each distinct [actid] should be counted once. When using simply Sum(bdg) values are calculated expected incorrectly:
Highlited values should be 1010 and 1110 in this case.
Does anyone have an idea how to get this working as required? Maybe there is some way to use related data tables or implement "UniqueSum" aggregation...
Thanks in advance
Finally found required solution. First, adding couple of calculated columns to have [bdg] value only for single row per unique [actid]:
rank: Rank(RowId(),"desc",[actid])
bdg_unq: If([rank]=1,[bdg],null)
Then we can use following expression as aggregation in cross table:
Sum([bdg_unq]) over (Intersect(All([Axis.Rows]),[Axis.Columns],[actid]))