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

     

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. 

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

Hi Anonymous ,

 

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

 

 

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

      Hi Anonymous ,

       

      If I understood correctly what you need is to change the metric used based on a slicer in this case you can create a parameter field:

      Then select the measures you want for your parameter:

      Now you can use the slicer to choose:

      On the Y axis instead of the measures use the parameter field.

       

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        That worked wonders, MFelix. Appreciate your assistance, thanks!