1 Reply Latest reply on Mar 1, 2016 8:16 PM by Shinichiro Murakami

    Change the LOD for a sum

    agathe bernardot

      Hello everyone, I am new to Tableau and I have a question for you :

      my excel table looks like this :

      region      country             brand           range             product            sales
      Europe    France             Flower          XXX                A                      100€

      Europe    France             Flower          XXX                B                     200€
      Europe    France             Flower          XX                   C                    150€

      Europe    France             Tree              Y                    D                     350€

       

      Europe   Germany          Flower          XXX                A                     500€

      Europe    Germany         Tree               Y                    D                     100€

       

       

      I have a table of data for my sales
      those figures of sales are given by product, by brand and by range.
      They are also divided by country and by region
      I would like to determine a threshold of sales for each range in each country and be able to display:
      - The number of times that my sales are below this threshold (by country, it will be 1 or 0 for each range) and to make the sum of that number by region or brand
      - The value of sales if it is less than the threshold for each country and to sum these values by region and range

      I used FIXED to create a space where I have the sales by range and by country and by establishing a threshold calculation, I can determine the number of times that my sales are below the threshold
      {FIXED [range], [country]: sum (sales)}                        gives [sales per country]

      IF [sales per country] <threshold
      Then [sales per country]
      End
      And Then I choose the aggregation "Total distinct"  and it gives me the number of times my sales are below the threshold in a country.

      But I do not know how to see the sales below the threshold for each range
      Indeed, Tableau sums all the lines, and when there are different products in the same range, it will sum the sales as many times as there is a product in this range. (if i have three products in this range, and I have determined the sum of the sales below the threshold for each range in each country, Tableau will add that value three times, and I only want one).

      Can you help me fix that problem?

       

      I was hoping I could create a measure where Tableau actually forgets that I have different products in the same range, that it would just replace the smallest LOD by the next

      I hope my question was clear enough,

      thank you so much for your help.