A challenge

[jduenyas]

Hi all

I have a pivot table which shows customer's name, Order number, Invoice number, Shipment Tracking number and Order Total.

There are times that an order is too large to physically fit in one box so it ships with 2 boxes each gets its own Tracking Number.

The Total of the order though should appear only once and not as shown as the Grand Total is skewed.

The Expression for the total is

=Sum(Amount)

I can correct that with the following expression

=Sum(Aggr(DISTINCT Amount, OrderNumber)

However, that drops one of the lines and only one Tracking number then appears like this:

But I need to see both Tracking numbers.

How can I achieve that.

Thanks

Josh

[chiru_thota]

I guess you can try below idea at Data model:

You can remove tracking number from fact

And create one more table just with OrderNO,Tracking NO.

For TrackingNO column Concatenate values if you have more .Then you will get only one row for each order NO

Example:

OrderNO         ,TrackingNO

0000100488    ,797560638262

0000100779 ,529456752584-529456752595

[Nicole-Smith]

I'm thinking something with FirstSortedValue()...

Maybe:

FirstSortedValue(aggr(sum(Amount),OrderNumber),OrderNumber)

EDIT: You may need a NODISTINCT in there too

FirstSortedValue(aggr(NODISTINCT sum(Amount),OrderNumber),OrderNumber)

[#4 · Accepted solution]

Hey Josh,

The reason you see only one tracking number when you use the second formula is because the value would be null() because of your aggregation of OrderNumber. There is nothing unique to aggregate OrderNumber for the second Tracking#.

Try unpressing null() and zero values. It will look like this:

Order#	Tracking#	Amount
12345	4589823	$6,500
12345	2345898	-

Assume this is a straight table. Similarly applies for Pivot as well.

Hope it helps. These will usually occur because of the way the Data Model is designed.

[jduenyas]

Thank you!

You are a genius!

That worked.