0 Replies Latest reply: Jun 12, 2018 10:43 PM by jialiang yao RSS

    Is there a chart function or formula I can create to calculate a measure based on a dimension not shown in the pivot table?

    jialiang yao

      Hi all,

       

      Assuming I have data as below, Department, Location, Billing Category, Revenue and Weightage. My measure = (Revenue * Weightage) grouped by Department, Location and Billing Category.

      before.PNG

       

      Lets say I am only concerned about Category - "N". Based on the weightages which might not = to 100%, the total savings across Location (30, TX, China) should be = $3173+$232+$4276 = $7681. The "Actual Savings" is a calculated measure based on Revenue and Weightage.


      If I want to show the total Across Department and Billing Category without Location as shown below, the measure will calculate across the new chart layout and give me the wrong "Actual Savings" result of $31,284.

       

      after.PNG

       

      Is it possible through any type of chart function, set analysis or what not, to factor in Location in my measure even if the dimension is not shown in the Chart.

       

      Thanks!