Forum Discussion

JonClemo's avatar
JonClemo
Regular Visitor
6 years ago

Index chart - specifying a particular data value

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 ...
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi JonClemo ,

 

Can you share sample data, or a sample pbix file.

 

Try this

Index =
DIVIDE (
    'Table'[Income],
    CALCULATE (
        SUM ( 'Table'[Income] ),
        'Table'[Year End] = 'Table'[Year End] - 1
    )
)

 

Regards,

Harsh Nathani

  • JonClemo's avatar
    JonClemo
    Regular Visitor
    6 years ago

    Thanks Anonymous 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'

     

    Here is some sample data 

    Test_Index Chart.xlsx 

     

    In pulling this out I realised that the base year reference would also need to change based on the category 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi JonClemo ,

       

      How do you determine the specified year in the past.

       

      Try this.

       

      Create  a separate Year Table. Add this as a slicer. This will detremine your index value.

       

       

       

       

      Create Measures

       

       

      Total Income = SUM('Table'[Income])

       

      Selected year Index = SELECTEDVALUE(YearTable[Year End])

       

      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)

       

      Regards,
      Harsh Nathani

      Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

       

      • JonClemo's avatar
        JonClemo
        Regular Visitor
        6 years ago

        Hi Anonymous 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 used

        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])

         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!