Forum Discussion

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
9 years ago
Hi Anonymous,

You can do ti using this simple steps:

Add a calendar table to your data but leave it as a standalone table (do not make any relationship with other tables)
Add a slicer to your report based on the Calendar Table
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
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

Super User

9 years ago

Hi Anonymous,

You can do ti using this simple steps:

Add a calendar table to your data but leave it as a standalone table (do not make any relationship with other tables)
Add a slicer to your report based on the Calendar Table

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

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

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
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
  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.
  - MFelix
    Super User
    9 years ago
    In my calculations I assumed you add a date, how are you setting up your filtering date?
    
    I can see in your print that you only have year and month.
    
    Regards
    MFelix