Forum Discussion

SQUILES's avatar
SQUILES
Helper I
5 years ago
Solved

Calculate Percent

HI, I need help. This is the situation i have: This is piece of my data filter by All Mondays of Dec-2020 and Jan-2021. date Balance1 Balance2 Total 12/7/2020  $     378.00  $  7,489.00 ...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi SQUILES ,

    Please update the formula of measure [Avg_Prv_Month]  and [Percent] as below:

    Avg_Prv_Month =
CALCULATE (
    AVERAGE ( Sheet1[Total] ),
    FILTER ( ALL ( Sheet1 ), WEEKDAY ( Sheet1[date], 2 ) = 1 ),
    PREVIOUSMONTH ( Sheet1[date] )
)
    Percent= DIVIDE([Avg_Prv_Month],[TotalBalance],0)-1

    Best Regards

amitchandak's avatar
amitchandak
Super User
5 years ago

SQUILES , few things are not clear, when need % of Monday, then you should divide sum of Monday by total, here you are dividing total by Monday

 

Monday = CALCULATE([TotalBalance],PREVIOUSMONTH('Date'[Date]), filter('Date',WEEKDAY('Date'[Date],2)=1))

last month = CALCULATE([TotalBalance],PREVIOUSMONTH('Date'[Date]))

 

last month % = divide([Monday],[last month])

Can you elaborate on calculations

SQUILES's avatar
SQUILES
Helper I
5 years ago

Sure, 

1. I need to calculate the AVERAGE of Total Balance of all MONDAYS of Previous Month:

Avg_Prv_Month =
CALCULATE(AVERAGE(Sheet1[Total]),WEEKDAY(Sheet1[date],2)=1,PREVIOUSMONTH(Sheet1[date]))

2. The percent = Average_Monday_Prev_Month / Total_Balance of each Monday of Current Month -1

Example: Average_Monday_Prev_Month=11,188.00

1/4/2021 = (11,188   /  8,987.00) -1

1/11/2021 = (11,188 / 13692.00) -1

1/18/2021 = (11,188 / 14703.00) -1

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi SQUILES ,

    Please update the formula of measure [Avg_Prv_Month]  and [Percent] as below:

    Avg_Prv_Month =
CALCULATE (
    AVERAGE ( Sheet1[Total] ),
    FILTER ( ALL ( Sheet1 ), WEEKDAY ( Sheet1[date], 2 ) = 1 ),
    PREVIOUSMONTH ( Sheet1[date] )
)
    Percent= DIVIDE([Avg_Prv_Month],[TotalBalance],0)-1

    Best Regards