Index chart - specifying a particular data value

Question

I am trying to create a summary table of income for a number of organisations over a number of years, categorised by size with a base year index column. The end result would look something like this containing results for multiple years

The report is intended for reuse and update so I am trying minimise the steps if any data is refreshed or visuals changed

I have a table with income for each organsiation for each year

I used a switch function to create a column that categorises that data into custom bands

I created a table using summarise that groups by year column and income by band with roll-up.

This gives me which is correct

I now want to add the index column

The formula for the income index would be [income]/[income total in 2008]

I know I could simply hardcode in the number but wanted to reference it. What I can’t understand if how to specify the reference or query in the formula to get a specific value returned. Essentially it would be

Select income where year end = 2008 AND category is blank (or total if I knew how to name the rollup row)

Where I got to is

Index = 'Table'[Income]/(CALCULATE(sum('Table'[Income]), 'Table'[Year End] =2008, 'Table'[Size By Income] = blank()))

But this only seems to work for the 2008 total row – otherwise it seems to not get the reference and return zero.

I've looked at the answer here which seems to use variables which I've not used in DAX before - what am I not seeing and what are my options? many thanks

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Creating-index-chart-showing-trend-from-the-baseline-year/m-p/725192#M1518

anonymous · Answer

Hi&nbsp;JonClemo&nbsp;,&nbsp;Can you share sample data, or a sample pbix file.&nbsp;Try thisIndex =
DIVIDE (
    'Table'[Income],
    CALCULATE (
        SUM ( 'Table'[Income] ),
        'Table'[Year End] = 'Table'[Year End] - 1
    )
)&nbsp;Regards,Harsh Nathani

jonclemo · Answer

Thanks&nbsp;Anonymous&nbsp;your suggestion produces the error below - but if I have understood it correctly I think it's trying to answer something slightly different which is 'current year divided by last year' whereas I am after 'current year divided by a specified year in the past'&nbsp;Here is some sample data&nbsp;Test_Index Chart.xlsx&nbsp;&nbsp;In pulling this out I realised that the base year reference would also need to change based on the category&nbsp;

anonymous · Answer

Hi&nbsp;JonClemo&nbsp;,&nbsp;How do you determine the specified year in the past.&nbsp;Try this.&nbsp;Create&nbsp; a separate Year Table. Add this as a slicer. This will detremine your index value.&nbsp;&nbsp;&nbsp;&nbsp;Create Measures&nbsp;&nbsp;Total Income = SUM('Table'[Income])&nbsp;Selected year Index = SELECTEDVALUE(YearTable[Year End])&nbsp;Measure 5 = 
var selyear = [Selected year Index]
var sumyear = CALCULATE(SUM('Table'[Income]),FILTER(ALL('Table'[Year End]), 'Table'[Year End] = selyear))
RETURN
DIVIDE ([Total Income],sumyear)&nbsp;Regards,Harsh NathaniDid I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)&nbsp;

jonclemo · Answer

Hi&nbsp;Anonymous&nbsp;That is certainly getting me a result so thank you - just need to do a little bit more testing to check the right figures are coming out. There is however something I don't understand. If I try and do this in the data table rather than as a table in the visual I feel I should be able to create a calculated column that provides the index. Simplifying without the slicer I usedTest1 = 
var index_year = 2008
var Baseline = calculate(SUM('Table'[Income]), FILTER(ALL('Table'[Year End]), 'Table'[Year End] = index_year))
RETURN
DIVIDE('Table'[Income], [Baseline])&nbsp;The result is I get a result of 1 in the column for all rows where Year End = 2008 but an empty field for all others. This implies to me that for 2008 it is calculating the Baseline variable correctly but in other years a value isn't being returned. I'm assuming this has to do with the variable being calculated by looking per row so when it filters for a 2008 value for rows other than 2008 nothing is returned but I feel I should be able to somehow set it to filter off the whole table. Any pointers would be helpful as I'm trying to improve my understanding not just copy solutions!

anonymous · Answer

Hi&nbsp;JonClemo&nbsp;,&nbsp;Based on what I have understood:&nbsp;Your index is dynamic and changes based on the year you select in a slicer. Table store static values and cannot generate dynamic values. Calculated Columns work best when you want to categorise or want to eat up on your memory.&nbsp;Suggest see this video :&nbsp;https://www.youtube.com/watch?v=03E0f-RyRn4https://www.youtube.com/watch?v=xV-KwuSDFMQhttps://www.youtube.com/watch?v=DwuAypulTLA&nbsp;Test1 = 
var index_year = 2008
var Baseline = calculate(SUM('Table'[Income]), FILTER(ALL('Table'[Year End]), 'Table'[Year End] = index_year))
RETURN
DIVIDE('Table'[Income], [Baseline])&nbsp;Will try my best to explain in simple terms. I hope i make sense.&nbsp;Check the Filter Part -&gt; You are filtering the table column&nbsp; [Year End] where Table[Year End] = 2018. So this will give you only rows of the table where year end = 2018. So incase your table has 100 rows out of which 5 rows are 2018. these 5 rows will be returned.Post that you are adding the income in these 5 rows.&nbsp;Now you are dividing the Income of all rows with the baseline (baseline has values only for 5 rows). So for other rows it is income divide by blank.&nbsp; And for 2018, you get 1. Now change the column type to decimal you may get 0.xx values in rows where year is 2018.&nbsp;I hope this made sense.&nbsp;Regards,Harsh Nathani&nbsp;

Forum Discussion

Index chart - specifying a particular data value

6 Replies

Related content

Multiple conditions Index

Index

Fixed Index Column

Index Key

Index Countif