Index relative to minimum selected year

[Anonymous]

Hi everyone, 

I'm making a chart where the vertical axis is index and horizontal axis is year. 
The value for each year is the indexed change from the first year in the chart. 

For example, if the selected years are 2000-2015, then 2014 = (sum of 2014 values / sum of 2000 values) *100.

So far I've been trying different versions of the measure below, but I can't get it to work. 

It seems to keep the filter for min(year) and thus returns 100 for all years. 

Measure=
divide(sum('Data'[value]),calculate(sum('Data'[value]),
filter('Data','Data'[Year]=calculate(min('Data'[Year]),allselected('TV Data')))))*100

Can this be done?

[Anonymous]

Hi again, 

I may have narrowed down the problem, but I still don't have a solution. 

calculate(min('Data'[Year]),allselected('TV Data'))

This shows the right year. Meaning if 2000 is the lowest selected year it shows in all years. 

However, it doesn't work as a filter in this expression:

calculate(sum('Data'[value]),
filter('Data','Data'[Year]=calculate(min('Data'[Year]),allselected('TV Data')))

I want it to return the sum of the values from the lowest selected year, but it simply shows the sums of each year. 

The return is the same as sum('Data'[Value]), which explains why the full expressions returns 100. 

Anyone knows why this filter is not applying? I'm sure there is a logical answer 🙂

[Zubair_Muhammad]

@Anonymous

Try removing the inner calculate

CALCULATE (
    SUM ( 'Data'[value] ),
    FILTER ( 'Data', 'Data'[Year] = MIN ( 'Data'[Year] ) ),
    ALLSELECTED ( 'TV Data' )
)

[Anonymous]

@Zubair_Muhammad

Thank you for responding. 

I tried your solution but unfortuently it still returns the sum of each individual year instead of the first. 

I've made a small mockup using this data:

Here is the return:

measure = calculate(sum(Data[Value]);FILTER(Data;Data[Year]=min(Data[Year]));allselected(Data))

In this case I want the measure to show 110 for all the rows whereas the value shows the sum of the specific year. 

The goal is to divide value by measure to show the difference from the lowest selected year. 

[v-yuta-msft]

Hi Danielgadhas,

I have test on your data but can't reproduce your issue as below, could you share your PBIX if possible?

 

Regards,

Jimmy Tao

[Anonymous]

Hi @v-yuta-msft

You are reproducing my problem. In your table I'd like the measure to show the value from 110 while keeping other filters applied. 

I've made some progress and I have a working expression with a static baseline year. Now I only have to make it dynamic, which is arguably the hardest part. 

Index (2014):=
  divide(
    sum('Data'[value]),
    calculate(sum('Data'[value]),
      filter(
        ALLEXCEPT('Data','Data'[Slicer1],'Data'[Slicer2]),
          'Data'[Year]=2014
      )
    )
  )
  *100

Apologies if I'm being unclear, but in Index (2014) I'd like to replace 2014 with the lowest selected year in the year slicer. 

[v-yuta-msft · Accepted solution]

Hi Danielgadhas ,

The result of a measure is based on current filtered context, so I'm afraid using a measure can't achieve your requirement, you should use calculate column instead.

Column = calculate(sum(Data[Value]), FILTER(all(Data), Data[Year]=min(Data[Year])))

Regards,

Jimmy Tao

[Anonymous]

@v-yuta-msft

Ok, I will consider using a calculated column and alternatively make more than one measure. 

Thanks for your help 🙂