Straight table dimension losing rows vs pivot table

[Anonymous]

I have a table called LO that has multiple rows per LO ID, for each Exception (PE), it has. I know that I have 141 PEs for 117 LO IDs.

When I add the dollar total using this formula: sum(aggr(sum(DISTINCT [Current  Amt]), [LO ID])) -- it correctly gives me the sum of the Current Amt for the 117 LO IDs, with no duplication of the amount for each LO ID that has more than one PE ID.

This formula is only working in a pivot table, when switch to a bar chart or straight table, the dollar number decreases. I have a filter in the dimension saying if the PE is inactive then dont show it, but when I put this in a straight table, any LO ID with a PE inactive gets removed entirely, instead of just removing one of the two PEs for that LO ID.

Any idea how to use the dimension but not have full LO IDs removed? thanks!

[bramkn]

A sample file would be helpful here.

[Anonymous]

I wish I could but there is too much sensitive data.

[bramkn]

Ok, so you can scramble it. (just change the data and not give all we only need a sample) also have you looked at the possibility to do this in script. The script is usually a lot easier to do advanced calculations.

[rubenmarin]

Hi Ashley, the straight table/bar chart has the same dimensions as the pivot table?

Also, it's reallty needed to sum the distinct amounts? That means: if a PE is 100 and another is 100, it sums 100. But 100 and 110 sums 210.

With a sample it's usually easier to get/give a correct answer faster.