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.

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

This ?


Create a summary table using the following DAX 

Calc_Table = 

Var _Calc=
SUMMARIZE(Book1,Book1[Date].[Month],"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

where Book1 is the source table and Calc_Table is the summary table.

Source data used is as follows :
Date,Time
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

Make sure to create a relationship for month columns across the two tables.

Regards,

Sachin Nandanwar 

  • 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