Dynamic indices with slicers

[Anonymous]

Hi guys,

I am new to Power BI so bear with me. I am trying to create prices indices where I can dynamically include products and the time frame. I have followed the thread below (see formula) and managed to create a version of such an index but not the one I am after. At the moment all my price data is in a single 'price' column alongside dates and products in adjacent columns, so that each price / date / product row is unique.  

The problem is if there are 2 products selected, starting prices £100/£200 and ending prices £300/£300, the output (base=100) is 200=(£300+£300)/(£200+£100)=200, and not 225 (average of £300/100 and £300/£200). I am after an equal weighted index, so the latter calculation. Is there any way to do this?

Many thanks

Price_Index =

VAR Date_Index =

CALCULATE ( MIN ('Prices historical'[Date]), ALLSELECTED ('Prices historical'))

RETURN

DIVIDE(sum('Prices historical'[Price]),CALCULATE(SUM('Prices historical'[Price]),'Prices historical'[DATE]=Date_Index))*100

https://community.powerbi.com/t5/Desktop/Index-100-Measure-with-dynamic-100-base-date/td-p/359938

[v-yingjl · Accepted solution]

Hi @Anonymous ,

You can try this measure:

Measure = 
VAR t =
    ALLSELECTED ( 'Prices historical' )
VAR tab =
    SUMMARIZE (
        'Prices historical',
        'Prices historical'[Attribute],
        "Re",
        VAR _attr = [Attribute]
        VAR _min =
            CALCULATE (
                SUM ( 'Prices historical'[Price] ),
                FILTER (
                    t,
                    'Prices historical'[Attribute] = _attr
                        && 'Prices historical'[Date]
                            = CALCULATE (
                                MIN ( 'Prices historical'[Date] ),
                                FILTER ( t, 'Prices historical'[Attribute] = _attr )
                            )
                )
            )
        RETURN
            SUM ( 'Prices historical'[Price] ) / _min
    )
RETURN
    AVERAGEX ( tab, [Re] * 100 )

I have filtered data from 2020/6/1-2020/6/5 and used a table visual to show the expected result:

Sample file is attached that you can refer: Sample.pbix 

Best Regards,
Yingjie Li

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

[Anonymous]

Excellent. Works perfectly as intended in the sample file. Thanks

[amitchandak]

@Anonymous Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

https://drive.google.com/file/d/1YnLMEtQhjftiqKgz7ewUdyjmRzz6Txrj/view?usp=sharing

Sure. PBIX here with a set of 3 'products' and 3 years of daily price data, and the index chart. In practice there will be 300+ products so any practical solution should not involve calculated columns for each product separately, unless there is an easy and low resource way to do this. I've created an unpivoted price column in the example file.

Thanks for your help.