0

Can I create a custom hierarchy of measures

I saw this article, https://community.pyramidanalytics.com/t/h7h92p1/how-to-combine-values-from-different-measures-in-one-charts.
Is it possible to create a custom hierarchy of measures? in my example, I built 4 measures in Formulate using formulas. 3 of the measures add up to the 4th. Could i create a custom hierarchy with these 4 measures?

5 replies

null
    • Senior Director of Product
    • Ian_Macdonald
    • 5 mths ago
    • Reported - view

    Hi Christopher,

    No, we do not support hierarchies in the Measures dimension.

    Ian

    • Customer Solutions Architect
    • Moshe_Yossef
    • 5 mths ago
    • Reported - view

    Hi  ,

    You could try to create a dimension table (suppose it's called MeasuresTable), which will have 2 columns, like this:

    Bottom Measure                                Top Measure

    Measure A                                          Measure Total

    Measure B                                          Measure Total

    Measure C                                          Measure Total

    And set up a hierarchy called "MeasureHierarchy" with 2 levels, Top Measure and Bottome Measure.

    Then create a formulate that basically says:

    switch([MeasuresTable].^[MeasureHierarchy].currentMember.Caption(),

    "Measure Total", ##formulate for the agg measure##,

    "Measure A", ##Formulate for Measure A##,

    "Measure B", ##Formulate for Measure B##,

    "Measure C", ##Formulate for Measure C##,

    NULL)

    I hope this works, also not sure if it will be very performant.

      • Christopher_Tecchio
      • 5 mths ago
      • Reported - view

       thank you. Yes, I also thought about creating a dimension table from the measures I built. I have not done this before in Pyramid so still thinking through it. I am assuming in the Model component of Pyramid, in my existing data model, I need to drag and drop an In-Memory database chip, chose my existing data model that has those measures built into it, and then do some ETL to create the new table with those two columns.

      I have not tested this yet but its part of my goals for the week to work on this problem statement so appreciate your replies and help guiding my start!

      • Customer Solutions Architect
      • Moshe_Yossef
      • 5 mths ago
      • Reported - view

       

      You don't need a new model file.

      Use the same model, add a data source with the role playing dimension table (MeasuresTable) - it can come from an excel file or txt or whatever, add it to your model, and the rest is Formulate.
      Good Luck!

    • Customer Solutions Architect
    • Moshe_Yossef
    • 5 mths ago
    • Reported - view

    Another Approach is to use formatting to differentiate the total from the components. using a bold header, a background color etc.

    Regards,

    Moshe

Login to reply

Content aside

  • Status Answered
  • 5 mths agoLast active
  • 5Replies
  • 53Views
  • 4 Following