
1. Re: list different values with different distance from average value
Ken Flerlage Jan 28, 2019 9:52 AM (in response to pietro.tamburini)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
Ken Flerlage Jan 28, 2019 9:55 AM (in response to Ken Flerlage)If my assumption is correct, then you should create a couple of calculated fields:
Overall Avg Discount
// Overall average discount for all products.
{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.

test (2).twbx 521.0 KB


3. Re: list different values with different distance from average value
pietro.tamburini Jan 29, 2019 12:24 AM (in response to Ken Flerlage)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
Ken Flerlage Jan 29, 2019 4:28 PM (in response to pietro.tamburini)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.

test (2).twbx 524.5 KB


5. Re: list different values with different distance from average value
pietro.tamburini Jan 30, 2019 12:10 AM (in response to Ken Flerlage)This is great! thank you very much!

6. Re: list different values with different distance from average value
pietro.tamburini Feb 10, 2019 1:13 PM (in response to Ken Flerlage)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
Ken Flerlage Feb 10, 2019 3:05 PM (in response to pietro.tamburini)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
pietro.tamburini Feb 11, 2019 12:48 AM (in response to Ken Flerlage)thanks. However, still a doubt remains: imagine that the 5rows 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 5fold).

9. Re: list different values with different distance from average value
Ken Flerlage Feb 11, 2019 6:46 AM (in response to pietro.tamburini)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.