1 Reply Latest reply on Jun 27, 2016 4:02 AM by Yuriy Fal

# Aggregating data into Bins at a data indenture level not in the display

Data hierarchy is Supplier --> Order Number --> Order Line --> Requirement

Each Requirement has an associated Net Value \$.

Raw data is loaded at the Requirement level.

The dashboard is at the Supplier level.

The calculation needs to SUM the Net Value \$ up to the Order Number level; then at the Order Number level (for each supplier) assess if the individual orders fall within defined "Bins", and provide a Count of Orders in each bin (for each supplier) and an average order value (in each bin for each supplier), and a % of the total (by value in each bin for each supplier).

For this example lets say we have 2 bins;  1 to 1000 and 1000 to 2000.

I can do basic bin calculations on the Net Value \$, but cannot figure out how to do this calculation based on a roll up at the Order Number Level; when the dashboard is at the Supplier Level.

This is just part 1 of a more complex calculation - but figured I would start here for a stepping stone and then see if I can figure out part 2 from what I learn

This cannot be done in an external database before import to Tableau because other information (Commodity and Program) are also captured at the requirement level and the dashboard needs to maintain the link back to the requirement level to allow for dynamic filtering between supplier, commodity, and program.

Thanks!

• ###### 1. Re: Aggregating data into Bins at a data indenture level not in the display

Hi Jeff,

Definitely you're better with Level-Of-Detail (LOD) calculations.

Something like this:

// [Net Value per Order Number]

{ FIXED [Order Number] : SUM( [Net Value] ) }

// [Net Value per Order Number Custom Bins]

// 1000 could be a Parameter aka Bin Size

INT( [Net Value per Order Number] / 1000 ) * 1000

// Average Order Value -- variant 1

AVG( [Net Value per Order Number] )

// Average Order Value -- variant 2

SUM( [Net Value per Order Number] ) / COUNTD( [Order Number] )

Hope it could help.

Yours,

Yuri

1 of 1 people found this helpful