Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JonClemo
Regular Visitor

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 containing results for multiple years

 

JonClemo_0-1596106319837.png

 

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

JonClemo_1-1596106319841.png

 

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-b...

6 REPLIES 6
harshnathani
Community Champion
Community Champion

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

Thanks @harshnathani 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'

JonClemo_0-1596108376276.png

 

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 

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.

 

1.jpg2.JPG3.JPG

 

 

 

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)

 

Hi @harshnathani 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!

Hi @JonClemo ,

 

Based on what I have understood:

 

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.

 

Suggest see this video : 

https://www.youtube.com/watch?v=03E0f-RyRn4

https://www.youtube.com/watch?v=xV-KwuSDFMQ

https://www.youtube.com/watch?v=DwuAypulTLA

 

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

 

Will try my best to explain in simple terms. I hope i make sense.

 

Check the Filter Part -> You are filtering the table column  [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.

 

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

 

I hope this made sense.

 

Regards,

Harsh Nathani

 

Thanks @harshnathani 

I'll have a watch. It's not the function of the formula that I am having trouble understanding I guess it is the way the variable works. 

 

As per your illustration - table has 100 rows, this is filtered to five then summed, which gives a value say 75. I am expected that value to then be set. As a result in the second part, each and every row will have the income for that row divided by 75. 

 

What is happening is as per your explanation is baseline only has a value for those five rows. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors