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

Hi,

 

Thanks a lot for your reply. What about how to include year please? That is there are several years of data in the table and it look like this formula is combining all the years to one.

 

Thanks a lot

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

Pretty easy. Create a new column that combines Month and Year from your source

 

MonthYear = FORMAT(Book1[Date],"MM" & "_" & Book1[Date].[Year])

and then change the Summarize table to include this column instead of just Month.

 

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​

 

 

Define a relationship across the two tables on Month_Year.

Sample data used :
Date,Time
28/05/2023,2
20/05/2023,100
21/05/2023,2
10/05/2023,2
01/02/2024,2
02/02/2024,2
03/02/2024,100
04/02/2024,100
05/02/2024,100
06/02/2024,100
15/05/2024,100
22/05/2024,100
02/06/2024,2
23/06/2024,100
22/06/2024,100
28/06/2024,100

Regards,
Sachin Nandanwar

 

  • 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