Forum Discussion
Resolver IDynamic index (Value) based on slicer
I am trying to recreate what I have in Excel within Power BI, shown in the table is a spreadsheet. In column G (Oct-21 this is a list which you can lick the dates from column A, column I (2010 base is the value selected in G). In row 3 cell D (And copied down) in Excel I have this formula - =IF(A3=G$2,H$2,B3*H$2/I$2) this will re base the index when the user selects in G 2 a new base value.
| A | B | C | D | E | F | base selection | 2010 base | |
| Date | Index (at base June 2010) | Percentage Change | at selected base in cell G2 | Base date selection | Oct-21 | 100 | 157.0 | |
| Dec-24 | 122.0 | 77.7 | ||||||
| Nov-24 | 101.0 | 64.3 | ||||||
| Oct-24 | 134.0 | 85.4 | ||||||
| Sep-24 | 180.0 | 114.6 | ||||||
| Aug-24 | 101.0 | 64.3 | ||||||
| Jul-24 | 132.0 | 84.1 | ||||||
| Jun-24 | 105.0 | 66.9 |
In my data within SQL I have my data stored in table_1 in the same format as above, I have an index column for each series (As per column B in Excel) as many series are stored in the table a date column in dd/mm/yyyy. What I am wanting to do is have a visual in Power BI of a table where the Index in each series can be re-based like the column D in Excel version above using a the value at the date the index selected in the slicer as a base value? Can anyone help please? My data in my SQL has columns Date column, Index, Series_Name, Series_Number (Series name and series number relate to each other)
Hi jaltoft ,
Thank you for reaching out to Microsoft Fabric Community.
I have used sample data that aligns with your scenario and calculated the base value accordingly. Please find the attached solution. Let us know if you need any further assistance.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thank you!!
7 Replies
v-sathmakuriCommunity Support
Hi jaltoft ,
Thank you for reaching out to Microsoft Fabric Community.
I have used sample data that aligns with your scenario and calculated the base value accordingly. Please find the attached solution. Let us know if you need any further assistance.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thank you!!
jaltoftThis is a really good solution v-sathmakuri and was really easy to follow, thanks.
- jaltoft
Hello v-sathmakuri can you help please this is similar to my above as I have not started a new request. The above answer was perfect however I have an addition to my Power BI I need to create a weighted index between 3 index, I have attached a spreadsheet to show you how it is calculated with test data on Excel. I need to be able to re-base the weighted index like has already been done in the example above. (This is the format in Excel my database is a list going down like you set it up).
In the Excel rows 1 and 2 E-G I have the weightings -This is an example of the formula for the index I need to make a weighting for with the three values with the ability for it to be also rebased as you have done before - =($E$2*100*C16/$C$4)+($F$2*100*E16/$E$4)+($G$2*100*G16/$G$4)
So within this formula E2 for example is the Weighting then it is the month C16 / C4 which is what I need a selected base for. Can you help?
1234 5678 9578 0.217927 0.156415 0.625658 DateColumn Series Number Index Series Number Index Series Number Index Pavement 01/01/2022 1234 130.9 5678 121.8 9578 110.8 01/02/2022 1234 131.5 5678 120.8 9578 110.1 01/03/2022 1234 131.3 5678 120 9578 109.4 01/04/2022 1234 130.4 5678 117.1 9578 109.1 01/05/2022 1234 128.9 5678 117.1 9578 109 - v-sathmakuri
Hi jaltoft ,
As per your sample data implemented the solution. Please find the attachment for the solution.
Thank you!!
- Akash_Varuna
Super User
Hi jaltoft For this create a slicer using the Date column and a measure to calculate the base value (BaseValue) from the selected date. Add another measure (RebasedIndex) to compute the index relative to the base using the formula: Index * 100 / BaseValue. Include these in a table visual alongside Date and Series_Name.
- jaltoft
How would I work out the base value (BaseValue) from the selected date?
- v-sathmakuri
Hi jaltoft ,
May I ask if the provided solution helped in resolving the issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you!!
