Forum Discussion

Sbudd's avatar
Sbudd
Helper II
2 years ago
Solved

Weekly comparison table

Hi 

Is the table below possible to be built in PBI? I can easily build the first 3 columns, but the difference and percent change columns are proving more difficult. I dont mind having it as seperate tables if that is easier. 

It will also be 2 weeks of data. 

 

Thanks in advance

 

 

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    Hi Sbudd -To includes the first three columns along with the "Difference" and "Percent Change" columns.

    You can create a measure for sales week1

    Sales_Week1 =
    CALCULATE(
    SUM('Sales'[Sales]),
    'Sales'[Week] = "Week 1"
    )

    Week2:

     

    Sales_Week2 =
    CALCULATE(
    SUM('Sales'[Sales]),
    'Sales'[Week] = "Week 2"
    )

    To find the difference of week1 and week2 and then find the percent change calculation with difference.

     

    Difference = [Sales_Week2] - [Sales_Week1]

     

    Percent_Change =
    IF(
    [Sales_Week1] = 0,
    BLANK(),
    ([Difference] / [Sales_Week1]) * 100
    )

     

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Your solution is so great  Anonymous 
    Hi, Sbudd 

    Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster. Thank you very much for your kind cooperation!

     

    Best Regards

    Jianpeng Li

     

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi Sbudd -To includes the first three columns along with the "Difference" and "Percent Change" columns.

    You can create a measure for sales week1

    Sales_Week1 =
    CALCULATE(
    SUM('Sales'[Sales]),
    'Sales'[Week] = "Week 1"
    )

    Week2:

     

    Sales_Week2 =
    CALCULATE(
    SUM('Sales'[Sales]),
    'Sales'[Week] = "Week 2"
    )

    To find the difference of week1 and week2 and then find the percent change calculation with difference.

     

    Difference = [Sales_Week2] - [Sales_Week1]

     

    Percent_Change =
    IF(
    [Sales_Week1] = 0,
    BLANK(),
    ([Difference] / [Sales_Week1]) * 100
    )

     

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!