Is there a chart function or formula I can create to calculate a measure based on a dimension not shown in the pivot table?
Assuming I have data as below, Department, Location, Billing Category, Revenue and Weightage. My measure = (Revenue * Weightage) grouped by Department, Location and BillingCategory.
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.
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.