Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
abadi_89
Frequent Visitor

Relative changes graph with first period as base period

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:

 

abadi_89_0-1612765600165.png

 

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

Thank you.

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @abadi_89 ,

According to your description, I create this data:

v-yangliu-msft_0-1612917193211.png

 

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

v-yangliu-msft_1-1612917193217.jpeg

 

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.

View solution in original post

7 REPLIES 7
v-yangliu-msft
Community Support
Community Support

Hi  @abadi_89 ,

According to your description, I create this data:

v-yangliu-msft_0-1612917193211.png

 

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

v-yangliu-msft_1-1612917193217.jpeg

 

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.

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
Super User
Super User

@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.

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"

 

abadi_89_0-1612769537683.png

 

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

It won't let me post the table! here is a screen shotSC.JPG

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.