cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
breeze87
Frequent Visitor

Indices calculation with dynamic variable

Hi all!

 

I have this calculated column to see the price indices based on a fixed year (2018) and I'd like to be able to change that variable based on a manual input or a list of values.

 

I tried with field parameters, and a slicer from an unlinked table and it's been a struggle 😅

 

 

Index Jan 2018 100 = 
VAR Yearbase = 2018
VAR Monthbase = 1
VAR feedstock = f_Feedstocks[Feedstock]
VAR numera = f_Feedstocks[Average M]
VAR denom = 
CALCULATE(
    AVERAGEX( f_Feedstocks, f_Feedstocks[Price] ),
    FILTER( f_Feedstocks, f_Feedstocks[Feedstock] = feedstock ),
    FILTER( f_Feedstocks, f_Feedstocks[Year] = Yearbase ),
    FILTER( f_Feedstocks, f_Feedstocks[Month] = Monthbase )
)
VAR raw =
DIVIDE(
    numera,
    denom
)
RETURN
raw * 100

 

 

breeze87_0-1670417274362.png

The table looks like this and it's doing the job fine for a predetermined year stored inside the variable.

 

Thanks in advance!

1 ACCEPTED SOLUTION

Hey @v-tangjie-msft,

 

One solution I will try is to have multiple columns calculated based on the years range and use a DAX measure to select the appropriate column to display based on the year selected but it will need to be unlinked from everything else.

VAR Yearbase = 2018

My intention, originally, was to have this number to be variable and not fixed.

 

Thanks for the feedback, I will leave the topic open for a couple of weeks and then accept your solution if nothing better shows up!

 

View solution in original post

4 REPLIES 4
v-tangjie-msft
Community Support
Community Support

Hi @breeze87 ,

 

According to your description, would you like to implement manual entry or select a certain year to filter your new columns?

For your problem, if you need to use slicers for filtering, then you can't use a new column, you should put the columns you need on top of the visual, and then write measures based on your logic, instead of calculated columns, which can't dynamically recognize slicer filtering. Second, you can also use the MAX() function to get the value selected by your slicer and apply it to your measure.

 

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution to help the other members find it more quickly. 

Hey @v-tangjie-msft,

 

One solution I will try is to have multiple columns calculated based on the years range and use a DAX measure to select the appropriate column to display based on the year selected but it will need to be unlinked from everything else.

VAR Yearbase = 2018

My intention, originally, was to have this number to be variable and not fixed.

 

Thanks for the feedback, I will leave the topic open for a couple of weeks and then accept your solution if nothing better shows up!

 

Hi @breeze87 ,

 

Calculated columns are not dynamically aware of slicer filtering, try changing to a measure. and create a year table, create a slicer with the year table, change the variable to var Yearbase=selectedcolumn('for slicer',"Year",[Year]), and change the filter in the measure to FILTER( f_Feedstocks, f_Feedstocks[Year] In Yearbase ).

 

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution to help the other members find it more quickly. 

The Index page already has a Yearly slicer to show different timeframes.breeze87_0-1670578363350.png

I think it would defeat the highlited slicer purpose, so I went with the approach mentioned in my first reply. With the extra table to use as a slicer like you mentioned.

Index Calc new = 
SWITCH(
    TRUE(),
    SELECTEDVALUE( 'Index Year'[Year] ) = 2018, 
        CALCULATE(
            AVERAGEX( f_Feedstocks, f_Feedstocks[Index Jan 2018 100] ),
            FILTER( f_Feedstocks, f_Feedstocks[View] <= [Selected View] )
        ),
        SELECTEDVALUE( 'Index Year'[Year] ) = 2019,
        CALCULATE(
            AVERAGEX( f_Feedstocks, f_Feedstocks[Index Jan 2019 100] ),
            FILTER( f_Feedstocks, f_Feedstocks[View] <= [Selected View] )
        ),
        SELECTEDVALUE( 'Index Year'[Year] ) = 2020,
        CALCULATE(
            AVERAGEX( f_Feedstocks, f_Feedstocks[Index Jan 2020 100] ),
            FILTER( f_Feedstocks, f_Feedstocks[View] <= [Selected View] )
        ),
        SELECTEDVALUE( 'Index Year'[Year] ) = 2021,
        CALCULATE(
            AVERAGEX( f_Feedstocks, f_Feedstocks[Index Jan 2021 100] ),
            FILTER( f_Feedstocks, f_Feedstocks[View] <= [Selected View] )
        )
)

 

hopefully the extra calculated columns don't affect the performance too much 🙂

 

Thanks for the help @v-tangjie-msft 

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors