cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## 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!

1 ACCEPTED SOLUTION
Super User

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

23 REPLIES 23
Super User

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Anonymous
Not applicable

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!

Super User

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?

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Anonymous
Not applicable

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.

Super User

Hi @Anonymous

If you can provide any more context would be great, if it possible to share a sample file it would be even better.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Super User

Hi @Anonymous ,

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Anonymous
Not applicable

Hi @MFelix, sorry for the late reply! I have made a Google Drive folder with some sample data. You should find it through this link:

Thanks for the help so far, appreciate it!

Super User

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.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Anonymous
Not applicable

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

Anonymous
Not applicable

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?

Super User

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Anonymous
Not applicable

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.

Super User

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Anonymous
Not applicable

Hi,

Following your advice, I set up another Date table, which is not linked to my data table.

The hierarchy filter is taking date from that separate Date table.

I have only one value for each of the month (on the first day), so for the date hierarchy I just keep month and year, removing the date since it is not needed.

I also tested again with a simple dataset and the problem still remains.

Super User

Hi @Anonymous,

The problem is that you Dates in calendar are sequencial and the ones in the Data are not.

Please create your Calendar Table based on the Data, add a new table with the following formula:

`DATES = DISTINCT(Data[Date])`

And then change your variable in the measure to this:

```VAR Date_index =
MAX ( DATES[Date] )```

Below see the result of this new setup and the old setup:

Regards,

MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Anonymous
Not applicable

Hi,

What I did:
1. Create a new date table:

`Dates = Distinct(Data[Date]) `

2. Create a new measure in the Data table

```Index 100 =
VAR Date_index =
Max (Dates[Date])
RETURN
SUM(Data[Value])
/ Calculate(max(Data[Value]);Data[Date]=Date_index)
*100```

3. The slicer gets value from the Date Table.

This is the result for me:

Super User

Hi @Anonymous,

You need to set-up your Date column as a Date not as a hierarchy, when you do the hierarchy it "turns on" the Time Intelligence and fill outs the rest of the dates that are missing.

Regards,

MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Anonymous
Not applicable

Hi @MFelix,

I have still one more question? With this solution, is there still a way to use hierarchy slicer?

I tried to do so, and it results in infinity:

In the data table, I create two new columns, which get the year and month from the date, and put them in hierarchy level. The hierarchy slicer is getting data from that year hierarchy. The data is still ok if the date period chosen include the date which I set as base for index. But if the period does not include, the data shows infinity.

Thanks you!

Super User

Hi @Anonymous,

Sorry for the late response, I have been looking at your question and once again waht is happening is that the time intelligence is kicking in when you create your calendar table, since you only have one date per month in your master data when you try to add the information based on a hierarchy slicer it goes wrong.

Once again you need to create the calendar table based on the values of your data:

`Calendar = DISTINCT(Data[Date])`

Make a relationship between this table and the Data table and add the Year and Month, put it in your slicer and should work as you need, for comparision I added a measure to the image below with the value of the selected date so you can see it always gets the correct value.

Regards,

MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Regular Visitor

I'm trying to use this solution and place the results by state on a map.  How can I have the map show the index value for each state as of the last date in the range selected?  For example, if I select a base date of 1/1/2012 and a data date range of 1/1/2012 - 1/1/2017, how can I map the index as of 1/1/2017 for each state?  Each state's index should calculate its own value on 1/1/2017 and divide by the value on 1/1/2012.

Thanks!