8 Replies Latest reply on May 9, 2019 12:34 PM by Jonathan Drummey

    LOD Calc and Table Calc - Any workarounds to get at the needed results?

    Mikhail Christiansen

      Hello everyone,


      In the following workbook, I have mocked up a work issue that I am having.


      I have one worksheet which shows the recent market share for a chosen product (user filter) by State. This market share is calculated by taking the sum of quantity for the recent four months for the chosen sub-category and dividing it by the sum of quantity for the recent four months for the whole state (all sub categories). This sheet works.


      Then, I have a second worksheet which attempts to show the market share over time. THIS IS WHERE THE ISSUE IS. We'll come back to this. (Top chart (Sum(Quantity) is not necessary, it's just there for your reference)


      Then, I have a dashboard with the worksheets above. The idea is that the user can select a country and then see the trend of the market share over time (second worksheet).


      The issue is that I cannot get a solution for the second worksheet. How can I show the market share (sum of the four values of subcategory A / sum of the four values of all subcategories ({Fixed State: Sum(Quantity)}as a trend over time.


      The difficulty is in mixing the action (dashboard selection, LOD calc (fix the denominator of market share to be all subcategories regardless of filter), and table calc (sum the current value and previous 3 of filtered subcategory to get numerator). I cannot figure out any way around this.


      Instead of beating my head against the wall for the next 3 days, I figured there is probably someone out there in the community who is a lot smarter than me.