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.

dateBalance1Balance2Total
12/7/2020 $     378.00 $  7,489.00 $    7,867.00
12/14/2020 $  4,814.00 $  7,614.00 $  12,428.00
12/21/2020 $  8,107.00 $  4,471.00 $  12,578.00
12/28/2020 $  8,731.00 $  3,148.00 $  11,879.00
1/4/2021 $  8,217.00 $     770.00 $    8,987.00

I need to Calculate the percent of All Monday of Prev Month and Each Mondays of Current Month:

Avg_Prev_Month_Monday = CALCULATE(AVERAGE('DATA '[Total]),WEEKDAY('DATA'[Date])=1,PREVIOUSMONTH('DATA'[Date]))
I got =  11,188.00 
Percent= DIVIDE([TotalBalance],[Avg_Prev_Month_Monday])-1
I got = 0.244909313 But my Table visualization bring me all dates with -100 even if a filter to show WeekDay =1.
 
 

Any suggest?
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

3 Replies

  • 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