Forum Discussion

jimpatel's avatar
jimpatel
Icon for Post Patron rankPost Patron
2 years ago
Solved

Percentage calculation for each months

Hi,   thanks for looking at my post.    I am trying to get below result. I wanted to see how many two's in respective months and divide by total number of rows in that month for percentage. In th...
  • SachinNandanwar's avatar
    SachinNandanwar
    2 years ago

    I made some changes but its not working

    Calc_Table = 

Var _Calc=
SUMMARIZE(Book1,Book1[MonthYear],Book1[Date].[Year],Book1[Date].[MonthNo],"OverAllCount",COUNT(Book1[Time]), "FilteredCount",COUNTROWS(CALCULATETABLE((Book1),Book1[Time]=2)))

Var _Pct_Calc=
ADDCOLUMNS(_Calc,"_Percentage",IF(DIVIDE([FilteredCount],[OverAllCount],0.00)=BLANK(),0.00,DIVIDE([FilteredCount],[OverAllCount],0.00)),"Date",DATE(Book1[Date].[Year], Book1[Date].[MonthNo],1))

RETURN _Pct_Calc


    I added the Date column to return  month & year in date format and then created a new calculated column for rolling averages

    Rolling3MonthAverage = 
CALCULATE(
    AVERAGE(Calc_Table[_Percentage]),                
    DATESINPERIOD(
        Calc_Table[Date].[Date],                 
        MAX(Calc_Table[Date].[Date]),           
        -3,                          
        MONTH                          
    )
)


    But for some reasons it keeps showing blanks.I have no idea why is this happening.

    Maybe some experts on the forum can help out.

jimpatel's avatar
jimpatel
Icon for Post Patron rankPost Patron
2 years ago

Much appreciated again. I am getting below error. Any idea please?

 

 

Thanks a lot

SachinNandanwar's avatar
SachinNandanwar
Icon for Impactful Individual rankImpactful Individual
2 years ago

Try this.Probably there is a white space issue 

Calc_Table =

Var _Calc=
SUMMARIZE(Book1,Book1[MonthYear],"OverAllCount",COUNT(Book1[Time]), "FilteredCount",COUNTROWS(CALCULATETABLE((Book1),Book1[Time]=2)))

Var _Pct_Calc=
ADDCOLUMNS(_Calc,"_Percentage",IF(DIVIDE([FilteredCount],[OverAllCount],0.00)=BLANK(),0.00,DIVIDE([FilteredCount],[OverAllCount],0.00)))

RETURN _Pct_Calc
  • jimpatel's avatar
    jimpatel
    Icon for Post Patron rankPost Patron
    2 years ago

    Thanks for your reply. But still same error

     

     

     

    Thanks a lot

    • SachinNandanwar's avatar
      SachinNandanwar
      Icon for Impactful Individual rankImpactful Individual
      2 years ago

      • jimpatel's avatar
        jimpatel
        Icon for Post Patron rankPost Patron
        2 years ago

        Thanks a lot. Is it possible to attach the solution copy please?

         

        thanks a lot

    • SachinNandanwar's avatar
      SachinNandanwar
      Icon for Impactful Individual rankImpactful Individual
      2 years ago

      This is very strange..It works ok for me


      The only change is the column name compared to yesterdays solution where you didnt had this issue.

      Book1[MonthName] is replaced by Book1[MonthYear]. Thats all.



       

      • jimpatel's avatar
        jimpatel
        Icon for Post Patron rankPost Patron
        2 years ago

        Hi,

         

        Sorry for one more question, What should i modify to get last 4 months percentage please? That is it will rolling 4 months. If i select July, it will be average of last 4 months percentage please. Any idea ? Really appreciated