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
- Value at other dates = Actual value at that date/Actual value at 1.1.2012 * 100

Do you have any suggestion on how to create the measures for calculating this? Thanks a lot!

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

     

23 Replies

  • MFelix's avatar
    MFelix
    Super 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
        Super 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
      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
        Super 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?