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

Thanks for your reply. But still same error

 

 

 

Thanks a lot

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

  • SachinNandanwar's avatar
    SachinNandanwar
    Icon for Impactful Individual rankImpactful Individual
    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

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

     

    thanks a lot

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

    Much appreciated

     

    Thanks a lot

  • 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

     

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

    Thanks a lot for your support. One last question, what should i modify to get last 4 months rolling figure please? That is if i select July 2024, it will be showing average of last 4 month data. Same for June and so on.

     

    Much appreciated again