Index bases in first period

[michielkuiper]

I would like compare local sales with nation wide sales, especially i would like to compare the growth and/or shrink.

But our local sales is about thousants and the nation wide sales is about millions. 

To compare the sales I woukld like to index the values.

For example:

periode	Sales	Sales_overall	Index_Sales	Index_Sales_overal
201926	2000	500000	100	100
201927	2002	500122	100,1	100,0244
201928	2003	500562	100,15	100,1124
201929	2004	500879	100,2	100,1758
201930	2008	500215	100,4	100,043
201931	2007	500300	100,35	100,06
201932	2009	500600	100,45	100,12
201933	2005	500892	100,25	100,1784
201934	2001	501023	100,05	100,2046
201935	2003	500999	100,15	100,1998
201936	2005	500700	100,25	100,14
201937	2010	500190	100,5	100,038
201938	2012	500333	100,6	100,0666
201939	2015	501202	100,75	100,2404
201940	2021	502358	101,05	100,4716
201941	2021	502463	101,05	100,4926
201942	2019	502589	100,95	100,5178

So, in Power Bi I only have the columns Period, Sales and Sales_overall.

I have to add columns Index_sales and Index_Sales Overal.

First i have add a new periodtable without any relations to other tables.

I made a slicer to select the index_period, for example i select period (week) 2019-26

Index_PeriodeID = MAX(Periode_Index[PeriodeID])

No i will sum each week the sales of that week with the sales of the index_PeriodeID

I tried several things and red several forums and websites but whitout any succes. 

An simular case can be found here, unfortunately I can't get it yet:

Solved: Re: Calculate index base 100 - Microsoft Power BI Community

I have got a table with:        Period, Sales, Sales_overall, Index_PeriodeID

So far so good, but it is not possible to settle the sales of that Period with the sales of Index_Period

Detail is that I have no real dates but dateID's (300052) or weeknumbers (201926 (yyyyww)).

I've done this before in Qlikview and it's not very complicated but I can't get it done in Power Bi.

i'll someone can help me en perhaps even other users! 

Thanks in advance. 

[v-janeyg-msft · Accepted solution]

Hi, @michielkuiper 

According to your description, I create a sample file to reproduce your scenes.

You need to create a index column first, then create two custom column to show the results.

Like this:

index = RANKX('Table',[periode],,ASC)

Column 2 =
VAR presales =
    SUMX ( FILTER ( 'Table', [index] = 1 ), [Sales] )
RETURN
    DIVIDE ( [Sales], presales ) * 100

Column 3 =
VAR presales =
    SUMX ( FILTER ( 'Table', [index] = 1 ), [Sales_overall] )
RETURN
    DIVIDE ( [Sales_overall], presales ) * 100

Best Regards

Janey Guo

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