Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Calculate index base 100

Hello all, I would like to add indexing feature in my report. In short, user via filter can select which date they want to set as base (for example 1.1.2012). Then: - Value at 1.1.2012 = 100 - V...
Need Help
  • MFelix's avatar
    MFelix
    9 years ago

    Hi Anonymous,

     

    You can do ti using this simple steps:

     

    1. Add a calendar table to your data but leave it as a standalone table (do not make any relationship with other tables)
    2. Add a slicer to your report based on the Calendar Table
    3. Create the following measure on your Data Table
      Index 100 =
VAR Date_index =
    MIN ( 'Calendar'[Date] )
RETURN
    SUM ( Data[Value] )
        / CALCULATE ( MAX ( Data[Value] ), Data[Date] = Date_index )
        * 100
    4. If you want to have dates previous to the selected date not being calculated make this changes to your measure:
      Index 100 =
VAR Date_index =
    MIN ( 'Calendar'[Date] )
RETURN
    SWITCH (
        TRUE (),
        MIN ( Data[Date] ) < Date_index, 0,
        SUM ( Data[Value] )
            / CALCULATE ( MAX ( Data[Value] ), Data[Date] = Date_index )
            * 100
    )

    Final result is below:

     

    Regards,

    MFelix

     

MFelix's avatar
MFelix
Icon for Super User rankSuper User
9 years ago

Hi Anonymous,

 

You can do ti using this simple steps:

 

  1. Add a calendar table to your data but leave it as a standalone table (do not make any relationship with other tables)
  2. Add a slicer to your report based on the Calendar Table
  3. Create the following measure on your Data Table
    Index 100 =
VAR Date_index =
    MIN ( 'Calendar'[Date] )
RETURN
    SUM ( Data[Value] )
        / CALCULATE ( MAX ( Data[Value] ), Data[Date] = Date_index )
        * 100
  4. If you want to have dates previous to the selected date not being calculated make this changes to your measure:
    Index 100 =
VAR Date_index =
    MIN ( 'Calendar'[Date] )
RETURN
    SWITCH (
        TRUE (),
        MIN ( Data[Date] ) < Date_index, 0,
        SUM ( Data[Value] )
            / CALCULATE ( MAX ( Data[Value] ), Data[Date] = Date_index )
            * 100
    )

Final result is below:

 

Regards,

MFelix

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi,

    Thanks for your suggestion. My dataset consists of many different items, and I need to have index value for each of the items. However, this methods sum up all value. Do you have any ideas how to solve that?

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      9 years ago

      Hi Anonymous,

       

      I just made a simple table that not very fancy calculation what type of calculation you want in the Total row?

       

      Regards,

      MFelix

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hello,

        Please ignore my previous question, that problem is solved. However, I encounter another one. Only the value at the date that I choose to index return correctly (100). Other returns infinity. 

         

         

        I use the same DAX as you suggested.


  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hello! 

    Thanks for a good answer! My dataset, however, consist of various goods, ranging from A-I. Do you have any idea on how i can filter the index based on this to show for example only A compared to C? 

    Thanks! 

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      3 years ago

      Hi Anonymous,

       

      When you mean that you want to have the comparition with A with C do you mean that the base value will be the the category or do you want it to continue to be the data but add next level of calculation to your index calculation? 

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi MFelix, 

        So I have used the formula you provided above to get indexed values for all of the goods A-I. But instead of seeing all of the goods and their respective indexes at once, I want to be able to filter out some of the goods so I for example only can see two indexed values and compare them. So I want to use the index formula, but add another level of calculations so I can filter on the different products.