Percentage evolution based on initial value 100

[Syndicate_Admin]

Hello to the whole community,

If someone could shed light on the solution to the problem I have, I would attack them:

It turns out that I would like to be able to make a graph to see the percentage variation of some products with respect to the initial value of January 2020 (January 2020=100%). I have a column where all the prices are per product per month. It turns out that this percentage difference should take into account that there are different products (product column) and the calculation for all the rows should not be made the same, but considering the type of product. I can't get any solutions through the editor.

Example below (get base column 100):

Product	Date	Price	Base 100 (what you want)
A	January 2020	90	100%
A	February 2020	100	x%
A	March 2020	110	x%
A	April 2020	120	x%
A	May 2020	140	x%
B	January 2020	200	100%
B	February 2020	240	x%
B	March 2020	230	x%
B	April 2020	250	x%
B	May 2020	270	x%

Thanks a lot

Greetings

[v-yangliu-msft · Accepted solution]

Hi  @Syndicate_Admin ,

Here are the steps you can follow：

1. Create measure.

Flag =
var _min=
CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),'Table'[Category]=MAX('Table'[Category])))
var _currentvalue=
CALCULATE(SUM('Table'[Price]),FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))&&
MONTH('Table'[Date])=MONTH(MAX('Table'[Date]))&&
'Table'[Category]=MAX('Table'[Category])
&&'Table'[Product]=MAX('Table'[Product])))
var _minvalue=
CALCULATE(SUM('Table'[Price]),FILTER(ALL('Table'),MONTH('Table'[Date])=MONTH(_min)
&&YEAR('Table'[Date])=YEAR(_min)
&&'Table'[Category]=MAX('Table'[Category])
&&'Table'[Product]=MAX('Table'[Product])))
return
(_currentvalue-_minvalue)/_minvalue+1)

2. Result:

When choosing Product, it will start from 100%:

Best Regards,

Liu Yang

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

[v-yangliu-msft]

Hi  @Syndicate_Admin ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _min=CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),'Table'[Product]=MAX('Table'[Product])))
var _currentvalue=CALCULATE(SUM('Table'[Price]),FILTER(ALL('Table'),MONTH('Table'[Date])=MONTH(MAX('Table'[Date]))&&'Table'[Product]=MAX('Table'[Product])))
var _minvalue=CALCULATE(SUM('Table'[Price]),FILTER(ALL('Table'),MONTH('Table'[Date])=MONTH(_min)&&'Table'[Product]=MAX('Table'[Product])))
return
(_currentvalue-_minvalue)/_minvalue+1

2. Result:

Does this match your expected result?

Best Regards,

Liu Yang

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

[Syndicate_Admin]

Estimado Liu,

Excellent, thank you very much, I am already very close to what I need, but I should add two more factors to the equation so that I get what I need:

1) In the database, in addition to months of work with years; from 2020 to 2022. How should I modify the DAX? At present the graph fits me like this. You can see that in the year 2021, all the data returns to 100% again.

2) When I select an individual product by filter the graph is transformed and in many cases I do not start from value 100 (see examples). It must be considered that the products are part of categories of families and subfamy in addition:

Thanks a lot

Greetings

WITH

[Syndicate_Admin]

The table that would look like the problem I have would look something like this:

Category	Product	Date	Price	Base 100
1	A	January 2020	90	100%
1	A	February 2020	100	x%
1	A	March 2020	110	x%
1	A	April 2020	120	x%
1	A	May 2020	140	x%
1	A	December 2020	150	x%
1	A	January 2021	160	x%
1	A	March 2021	165	x%
1	A	July 2021	173	x%
1	A	December 2021	180	x%
1	A	January 2022	185	x%
1	B	January 2020	200	100%
1	B	February 2020	240	x%
1	B	March 2020	230	x%
1	B	April 2020	250	x%
1	B	January 2021	265	x%
1	B	December 2021	270	x%
1	B	January 2021	290	x%
2	C	January 2020	5	100%
2	C	January 2021	7	x%
2	C	January 2022	8	x%
2	D	January 2020	24	100%
2	D	January 2021	26	x%
2	D	January 2022	31	x%

Thanks a lot

Greetings

WITH

[parry2k]

@Syndicate_Admin it is very hard to understand the image you shared, it will be easier if you share the data in the table format and explain what you are looking for.

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Syndicate_Admin]

Done