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

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.

7 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

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

      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's avatar
        amitchandak
        Icon for Super User rankSuper User
        5 years ago

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

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