Relative changes graph with first period as base period

[abadi_89]

Hello guys.

I am struggling with this one, my data is (Date, Product, Quantity, Value)

What I need is a graph shows the average price relative change (Quantity/Value) and use the first period in the selection as 100% like this:

I tried many stuff but couldn't manage to make it comparing the change to base period.

Thank you.

[Anonymous · Accepted solution]

Hi  @abadi_89 ,

According to your description, I create this data：

Here are the steps you can follow：

1. Create measure.

First find the percentage of the smallest month, convert the percentage of the smallest month to 100%, and compare the other months to change

Measure =
var _Q=CALCULATE(SUM('Table'[Quantity]),FILTER('Table','Table'[date]=MAX('Table'[date])))
var _V=CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[date]=MAX('Table'[date])))
var _min=MINX(ALL('Table'),[date])
var _3Q=CALCULATE(SUM('Table'[Quantity]),FILTER(ALL('Table'),'Table'[date]=_min))
var _3V=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[date]=_min))
var _value=DIVIDE(_3Q,_3V)
var _percent=DIVIDE(1,_value)
var _result= DIVIDE(_Q,_V)*_percent
return _result

2. Result

You can downloaded PBIX file from here.

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.

[abadi_89]

Sorry for the delay, your solution work perfectly in the file you shared!

It did not work in my case maybe because mine in different tables

Do you mind if I ask you what if the columns are in different tables?

Calendar (table) - Date (Column)

Invoice (table) - Value (Column)

Invoice (table) - Quantity (Column)

Once again all what I need is an average price (quantity/value) starting from 100% as you did before.

[amitchandak]

@abadi_89 , If you do not have date then create a period table(separate) and create a rank on year period, And try measure like

Period Rank = RANKX(all('Period'),'Period'[year period],,ASC,Dense)  // YYYYPP
This Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])))
Last Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])-1))

diff = [This Period ]-[last Period ]
diff % = divide([This Period ]-[last Period ],[last Period ])

In case you have date then create a date table

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

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

[abadi_89]

Hi again,

Thank you for the help but it did not work! it still don't consider first period as base period for comparsion.

I used the default sample data that comes with Power BI desktop, once you open blank Power BI desktop you see option "Data sample"

[amitchandak]

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

[abadi_89]

It won't let me post the table! here is a screen shot

[Ashish_Mathur]

Hi,

In the last image that you have shared, how will you calculate avrage price?  Where is the quantity column.  Share the link from where i can download your PBI file.