9 Replies Latest reply on Feb 11, 2019 6:46 AM by Ken Flerlage

# list different values with different distance from average value

Hi all,

I want to list different suggested prices based on the average discount that has been applied to a specific product. As such, the 5-rows list will comprehend the average discount -2%, the average discount -1%, the average discount +1% and the average discount +2%.

Here below a rendering of how it should look like: I am finding it hard to list items in this way. I think I should create a calculated value to represent avg(discount) +-x%, but I really do not know how to represent it this way.

Plese see the attached file for related data.

• ###### 1. Re: list different values with different distance from average value

So, to clarify, you first want to find the overall average discount for all of the products? You then want to find the different between each product's average discount from the overall average discount?

• ###### 2. Re: list different values with different distance from average value

If my assumption is correct, then you should create a couple of calculated fields:

Overall Avg Discount

{FIXED : AVG([Discount])}

Avg Discount Difference

// Difference between the overall average and the actual average for each product.

AVG([Overall Avg Discount])-AVG([Discount])

Then you can add Avg Discount Difference to the view. See attached.

• ###### 3. Re: list different values with different distance from average value

Hi Ken,

no, I am not looking for the difference between each product's average from the overall. I want to find the average discount of each product (as you can see from the attached image, I would then use a filter to display one product at a time) and then, on the other rows, display the average discount minus two (in the image the avg discount is 4.05%, in the first row I would have 4.05%-2%=2.05%), then the avg discount minus one (3.05%), the avg discount itself in the central and third row, then avg discount plus one (5.05%) and plus two (6.05%).

Once I have listed this different "ranges" of the average discount, in the next column I would have the list price multiplied for the discount (in the image, the values spanning from 1.47, corresponding to discount rate of 2.05%, to 1.41, corresponding to 6.05%).

• ###### 4. Re: list different values with different distance from average value

Got it. This is a bit tricky because you need to create 5 rows for every 1 row. I'd start out by unioning your data to itself a total of 5 times. Then we'll create a couple of calculated fields:

Product Avg Discount

// Average discount for each product.

{FIXED [Product Code]: AVG([Discount])}

Discount ID

// Numeric identifier of the discount.

CASE [Table Name]

WHEN "SAMPLE" THEN 1

WHEN "SAMPLE1" THEN 2

WHEN "SAMPLE2" THEN 3

WHEN "SAMPLE3" THEN 4

WHEN "SAMPLE4" THEN 5

END

New Discount

// Numeric identifier of the discount.

CASE [Table Name]

WHEN "SAMPLE" THEN [Product Avg Discount]-.02

WHEN "SAMPLE1" THEN [Product Avg Discount]-.01

WHEN "SAMPLE2" THEN [Product Avg Discount]

WHEN "SAMPLE3" THEN [Product Avg Discount]+.01

WHEN "SAMPLE4" THEN [Product Avg Discount]+.02

END

Final Price

// Final price after accounting for the discount

[List Price]*(1-[New Discount])

Then you can build your view as shown below: Hope that helps. See attached workbook.

• ###### 5. Re: list different values with different distance from average value

This is great! thank you very much!

• ###### 6. Re: list different values with different distance from average value

Hi Ken,

I am trying to implement your directives but I have a few doubts that need clarification.

First, I do not know how to union data to itself.

Then, does the union cause the data to grow fivefold? I might get wrong insights in that way.

Lastly, I may not the formula below because I want to display the series of discounts filtered for the customer (different customers would have different avg discount)

can you help me out with this?

thanks!

Product Avg Discount

// Average discount for each product.

{FIXED [Product Code]: AVG([Discount])}

• ###### 7. Re: list different values with different distance from average value

Here's how to do a union: Union Your Data - Tableau

And, yes, this will basically duplicate your data 5 times so if you start with 1 million records, for example, the result in Tableau will be 5 million records.

If you need to account for customer, you'll have to add the customer ID into the FIXED LOD. Something like: {FIXED [Customer], [Product Code]: AVG([Discount])}.

If you're not familiar with FIXED LODs, I'd highly recommend reading the following: FIXED Level of Detail Expressions - Tableau. Also, LODs can have an impact on certain things, especially dimension filters, so you'll need to be very wary of the Tableau Order of Operations. For more on this, see Tableau's Order of Operations - Tableau

• ###### 8. Re: list different values with different distance from average value

thanks. However, still a doubt remains: imagine that the 5-rows viz t is just a part of a greater dashboard which relies on the same data source. I am afraid that unioning the data will jeopardize the integrity of the analysis (e.g. I will include a timeline showing the revenues coming from each customer on a monthly basis: replicating the data 5 times will increase the revenue data 5-fold).

• ###### 9. Re: list different values with different distance from average value

Yes that's true. There are a couple of ways you could deal with that. But my suggestion would be to filter those additional worksheets so that they include only one of the "Tables", thus they will only show one set of data instead of all 5.