10 Replies Latest reply on Jan 21, 2016 7:19 AM by Joe Oppelt

    Challenge use Tableau only and not excel: Calculate Aggregated measure at higher level using Weighted average, use it in calculation field at lower level and summarise result at medium level

    Giulio Rossi

      I have a set of data: invoices by supplier and suppliers grouped by category

      I would like to use only tableau rather than Excel for calculations

      1) Calculate Aggregated measure a at higher level using Weighted average (CATEGORY OF SUPPLIERS)

      2) use this measure in calculation formula at lower level (INVOICE FOR EACH SUPPLIER) and

      3) summarise result at medium level (SUPPLIER)

      SupplierCategoryinvoice numberamountIDDDPDPT
      ac1i110001-Feb03-Mar08-Mar30
      ac1i25002-Mar01-Apr16-Apr30
      ac1i33013-May12-Jun27-Jun30
      bc1i42025-Aug09-Oct22-Oct45
      bc1i51001-Apr16-May04-Jun45
      bc1i623017-Nov01-Jan23-Jan45
      bc1i720010-Dec24-Jan28-Jan45
      cc2i85008-Mar07-May16-May60
      cc2i9100022-Jul20-Sep22-Sep60
      dc2i102001-Feb02-May29-May90
      dc2i113026-Mar24-Jun22-Jul90
      dc2i121018-May16-Aug23-Aug90
      dc2i131010-Jul08-Oct21-Oct90

      Calculate DTP as DP-ID for each invoice

      SupplierCategoryinvoice numberamountIDDDPDDTP
      ac1i110001-Feb03-Mar08-Mar        35.74
      ac1i25002-Mar01-Apr16-Apr        45.14
      ac1i33013-May12-Jun27-Jun        45.72
      bc1i42025-Aug09-Oct22-Oct        58.35
      bc1i51001-Apr16-May04-Jun        64.48
      bc1i623017-Nov01-Jan23-Jan        67.36
      bc1i720010-Dec24-Jan28-Jan        49.79
      cc2i85008-Mar07-May16-May        69.03
      cc2i9100022-Jul20-Sep22-Sep        62.73
      dc2i102001-Feb02-May29-May     117.09
      dc2i113026-Mar24-Jun22-Jul     118.59
      dc2i121018-May16-Aug23-Aug        97.18
      dc2i131010-Jul08-Oct21-Oct     103.10

      I want to be able to calculate a weighted average DTP by category, I have seen several tutorial and it should be easy if you want to create a table. I would like to calculate this however without showing the result in a tableau table

       

      Row LabelsSum of PT
        weight
      Sum of DTT weightSum of DTP
        weight
      Sum of amountWA DTP
      c1261002610034464.3953764053.85062
      c2693006930074086.65605112066.1488
      Grand
        Total
      9540095400108551.0514176061.67673

       

      use this number back  at invoice level to calculate a benefit on each invoice based on the difference of DTP and WADTP (only for positive amounts)

      SupplierCategoryinvoice numberamountIDDDPDDTPDTP weightWA DTP CATDTP -WADTP CATyear Benefit
      ac1i110001-Feb03-Mar08-Mar        35.74         3,57453.85061777-      18.110
      ac1i25002-Mar01-Apr16-Apr        45.14         2,25753.85061777-        8.710
      ac1i33013-May12-Jun27-Jun        45.72         1,37253.85061777-        8.130
      bc1i42025-Aug09-Oct22-Oct        58.35         1,16753.85061777          4.500.246345
      bc1i51001-Apr16-May04-Jun        64.48             64553.85061777        10.630.291265
      bc1i623017-Nov01-Jan23-Jan        67.36       15,49253.85061777        13.518.510474
      bc1i720010-Dec24-Jan28-Jan        49.79         9,95853.85061777-        4.060
      cc2i85008-Mar07-May16-May        69.03         3,45266.14880004          2.880.394869
      cc2i9100022-Jul20-Sep22-Sep        62.73       62,73366.14880004-        3.420
      dc2i102001-Feb02-May29-May     117.09         2,34266.14880004        50.942.791252
      dc2i113026-Mar24-Jun22-Jul     118.59         3,55866.14880004        52.444.310092
      dc2i121018-May16-Aug23-Aug        97.18             97266.14880004        31.030.850162
      dc2i131010-Jul08-Oct21-Oct     103.10         1,03166.14880004        36.961.012478

       

      and summarise data in at supplier level in a tableu sheet

       

       

      CategorySupplierPTSum of amountSum of year
        Benefit
      WA DTP
      c1a301800.0040.015
      c1b454609.0559.26456
      c2c6010500.3963.03275
      c2d90708.96112.8896

      can this be done all in Tableau without using excel as a support?

       

      (see excel file)

       

      see tableau file that shows how far i got