cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
arvindyadav Member
Member

How to find weekly growth rate in power bi

Hi Team,

 

How can I get weekly growth rate in power BI?

example:-

Weekly Growth Rate
Week numProfitGrowth Rate
450000%
554669%
62452-55%
724578902%
82120-91%
920000843%
1015246-24%

 

Thanks,

Arvind

7 REPLIES 7
Nick_M Established Member
Established Member

Re: How to find weekly growth rate in power bi

Using the data you supplied you can do the following:

Total Profit = SUM ( Table1[Profit] )

Previous Week Profit = 
CALCULATE(  
    [Total Profit], 
    FILTER(
            ALL ( Table1), 
            MAX(Table1[Week num]) -1 = Table1[Week num] )
    )

Weekly Growth Rate = 
IF ( NOT (ISBLANK( [Previous Week Profit] )),
 DIVIDE( [Total Profit] , [Previous Week Profit] ) -1)

Final Table.png

 

You dont need to ahve the Previous Week Profit measure on the table, just left it on there. Also, Assuming you have more than one year of data you will need to account for that as well. Meaning in the Previous Week Profit need to account for the year:

Previous Week Profit = 
CALCULATE(  
    [Total Profit], 
    FILTER(
            ALL ( Table1), 
            MAX(Table1[Week num]) -1 = Table1[Week num] ),
            MAX ( Table1[Year]) = Table1[Year]
    )
arvindyadav Member
Member

Re: How to find weekly growth rate in power bi

Hi @Nick_M ,

 

It works but in 5th and 6th week it calculating wrong I guess Can you please suggest me what should I do?

 

Not Working fine.PNG

arvindyadav Member
Member

Re: How to find weekly growth rate in power bi

Hi @Nick_M ,

 

When I clicked on any slicer then it will give me the wrong answer.

 

wrong answer.PNG

Nick_M Established Member
Established Member

Re: How to find weekly growth rate in power bi

the numerator just needs to be the Total Profit measure, which will be the total profit in the current filter context. Then denomniator is the Previous Week Profit measure, which will take the week number in the current filter context and go  back 1 week and return that Total Profit. THen from there subtract the whole thing by 1 to get the %.  

 

The If ( not ( isblank).. just ensures that there must be a previous week profit in order to have a weekly growth rate:

Growth Rate Week, Correct Num.png

 

I dont seem to have that problem when I use Week Number as a slicer

arvindyadav Member
Member

Re: How to find weekly growth rate in power bi

Hi @Nick_M ,

 

But when I select so,e account or account holder in its seems to wrong value in growth rate column.

Nick_M Established Member
Established Member

Re: How to find weekly growth rate in power bi

I see. It is because the Filter ( ALL ( Table) in the Previous week profit  was removing all the filters so it would give the previous week profit for all the accounts. If you change it to the following (where it only removes the the Week Number column from the current filter context it shoud work:

      Previous Week Profit = 
      CALCULATE(  
          [Total Profit], 
            FILTER( ALL ( Table1[Week num]), 
            MAX(Table1[Week num]) -1 = Table1[Week num] )
        )

Growth Rate Week, Correct Num.png

arvindyadav Member
Member

Re: How to find weekly growth rate in power bi

Hi @Nick_M ,

 

Thanks for your reply Your solution is work for me but when I click on any specific account from Slicer then it reflects the wrong %. Please find an IMAGE.

when you focus on week no 7 & 8 you will know what exactly happens in the formula.

 


Wrong Value When I click on specific Account name in Slicer.PNG