Forum Discussion

zakkyang's avatar
zakkyang
Frequent Visitor
5 years ago
Solved

groupby multiple columns and calculate the difference base on the date

Hi,


There are columns like: week number, product, country and sales. I want to group by product and country to calculate the difference between previous week (order by week in the group). Any idea? Thank you in advance.

Need Help
Tips and Tricks
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi zakkyang 

    If the columns you mentioned are all from the same table, you could create a measure like below to calculate the difference between each week and its previous week. And put product, country, week columns, sum of sales and the Difference measure into a matrix visual to check the differences.

    Difference = 
VAR _thisWeekNumber = SELECTEDVALUE('Table (2)'[Week Number])
VAR _thisWeekSales = SUM('Table (2)'[Sales])
VAR _previousWeekSales = CALCULATE(SUM('Table (2)'[Sales]),ALLEXCEPT('Table (2)','Table (2)'[Product],'Table (2)'[Country]),'Table (2)'[Week Number]=_thisWeekNumber-1)
RETURN
IF(ISBLANK(_previousWeekSales),BLANK(),_thisWeekSales - _previousWeekSales)

    Download the attached .pbix for details.


    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.

4 Replies

  • zakkyang's avatar
    zakkyang
    Frequent Visitor
    5 years ago

    Hi ,

     

    I got lost in your contents. Could you give a direct solution with code? I am new to PowerBI and please allow me to digest through your code directly. Thanks a lot.

     

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi zakkyang 

    If the columns you mentioned are all from the same table, you could create a measure like below to calculate the difference between each week and its previous week. And put product, country, week columns, sum of sales and the Difference measure into a matrix visual to check the differences.

    Difference = 
VAR _thisWeekNumber = SELECTEDVALUE('Table (2)'[Week Number])
VAR _thisWeekSales = SUM('Table (2)'[Sales])
VAR _previousWeekSales = CALCULATE(SUM('Table (2)'[Sales]),ALLEXCEPT('Table (2)','Table (2)'[Product],'Table (2)'[Country]),'Table (2)'[Week Number]=_thisWeekNumber-1)
RETURN
IF(ISBLANK(_previousWeekSales),BLANK(),_thisWeekSales - _previousWeekSales)

    Download the attached .pbix for details.


    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.

    • Coriel-11's avatar
      Coriel-11
      Icon for Resolver II rankResolver II
      5 years ago

      Hi v-jingzhang 

      Thank you so much much for this. I spent much of yesterday pulling my hair out over one similar to this and couldn't find a similar question in community.

       

      The closest I got was using a measure like this:

      diff = 
CALCULATE (
        SUM ( 'Key Webpages'[Pageviews] ), 'Key Webpages'[Months back] 
    )
        - CALCULATE (
            SUM ( 'Key Webpages'[Pageviews] ), 'Key Webpages'[Months back] -1 
        )

      I adjusted yours and it worked perfectly! Would you mind telling, is the reason you use variables here (aside from neatness/simplicity) that it enables you to use the SELECTEDVALUE function where it wouldn't otherwise work (i.e. in a T/F expression)?