locked
Creating Filtered Measures RRS feed

  • Question

  • Hello All -

    I have a question regarding using measures.
    My dataset (changed for confidentiality) consists of sales from vegetables and fruits. Fruits can be split up into either apples or oranges. So my categories are: fruit, vegetable, and sub-categories are: vegetable, apple, orange.

    When reporting on the sales I want my orange and apple sales to add up to my total fruit sales. However I also report on the expected growth rate. This growth rate is the same for oranges, apples, and total fruit. However there is a different growth rate for vegetables.

    I am reporting this data in a table with slicers. I want to be able to show sales and growth rate in the same table. The issue is while sales is additive for sub-categories, growth rate is not.

    How can I create a measure that allows me to say that growth rate should be equal when filter on fruit, fruit - orange, and fruit - apple?

    I have tried formulas such as SUMX, FILTER, FILTER(VALUES , etc.

    Any help with this would be much appreciated!

    NOTE: My data is stacked so the growth rate is listed twice (once for fruit - orange, once for fruit -apple). Simply summing the growth rate will not work because of this. Although it will be correct for vegetables, it will be double what it should be for fruit.


    • Edited by ClaireEFG Monday, January 5, 2015 2:35 PM
    Monday, January 5, 2015 2:18 PM

Answers

  • Hey,

    assuming that your model looks a little bit like this

    and the "facttable" factGrowthRate is hidden from your client tool, you can create a Measure (associated to the table "factSales" that looks like this:

    =calculate(

    averagex(dimProduct;related(factGrowthRate[growthrate]))

    )

    Then you get this

    Hope this helps

    Tom

    • Proposed as answer by Michael Amadi Monday, January 5, 2015 4:02 PM
    • Marked as answer by Charlie Liao Tuesday, January 6, 2015 8:10 AM
    Monday, January 5, 2015 3:45 PM

All replies

  • Can you provide a mockup of your sample data along with an example of what the finished product should look like?
    Monday, January 5, 2015 3:32 PM
  • Hey,

    assuming that your model looks a little bit like this

    and the "facttable" factGrowthRate is hidden from your client tool, you can create a Measure (associated to the table "factSales" that looks like this:

    =calculate(

    averagex(dimProduct;related(factGrowthRate[growthrate]))

    )

    Then you get this

    Hope this helps

    Tom

    • Proposed as answer by Michael Amadi Monday, January 5, 2015 4:02 PM
    • Marked as answer by Charlie Liao Tuesday, January 6, 2015 8:10 AM
    Monday, January 5, 2015 3:45 PM
  • Hi Tom - That worked! Thank you so much!
    Monday, January 5, 2015 4:02 PM