Forum Discussion

abadi_89's avatar
abadi_89
Frequent Visitor
5 years ago
Solved

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 th...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
abadi_89
Frequent Visitor
5 years ago

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.