cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
MFelix
Super User
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:

 

Index_100.png

Regards,

MFelix

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

23 REPLIES 23
MFelix
Super User
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:

 

Index_100.png

Regards,

MFelix

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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? 

torkild_oo_0-1663659345381.png

torkild_oo_1-1663659392970.png

Thanks! 

Hi @torkild_oo,

 

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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. 

Hi @torkild_oo 

 

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @torkild_oo ,

 

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.

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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: 
https://drive.google.com/drive/folders/101lTXUDi-603BFeJxx_DzX2Hcl_a2jID?usp=sharing

 

Thanks for the help so far, appreciate it! 

Hi @torkild_oo ,

 

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:

MFelix_0-1664365132009.png

Then select the measures you want for your parameter:

MFelix_1-1664365158880.png

Now you can use the slicer to choose:

MFelix_2-1664365189850.png

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

 

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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?

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


Did I answer your question? Mark my post as a solution!

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. 

Capture.PNG

 

 

I use the same DAX as you suggested.


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


Did I answer your question? Mark my post as a solution!

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.Capture.PNG

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:

Index_New.png

 

Regards,

MFelix

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi,

Could you please help me to check it? Somehow it still does not work for me.

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:
Capture.PNG

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


Did I answer your question? Mark my post as a solution!

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:
Capture.PNG
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!

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.

 

indexx.png

 

Regards,

MFelix

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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!

Helpful resources

Announcements
Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.