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.

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
2 years ago

Hi,

I am not sure if I understood your question correctly, but if you are looking for creating calculated column, please try something like below.

 

PerMonthPercentage CC = 
VAR StartDate = EOMONTH('TABLE1'[Date], -1) + 1
VAR EndDate = EOMONTH('TABLE1'[Date], 0)
VAR CountWithCondition = 
    CALCULATE(
        COUNTROWS('TABLE1'), 
        'TABLE1'[Date] >= StartDate,
        'TABLE1'[Date] <= EndDate,
        'TABLE1'[Time] = 2
    )
VAR TotalCount = 
    CALCULATE(
        COUNTROWS('TABLE1'), 
        'TABLE1'[Date] >= StartDate,
        'TABLE1'[Date] <= EndDate,
        'TABLE1'[Time] >= 0
    )
RETURN
    DIVIDE(CountWithCondition, TotalCount, 0)
  • jimpatel's avatar
    jimpatel
    Icon for Post Patron rankPost Patron
    2 years ago

    Thanks a lot for your input. 

    Much appreciated. But formula works fine if Time =2. In the below example after using your formula, what i am expecting is 70% for April 2024. Reason is there are seven 100 and 3 empty or 0. So 7/10 will be 70%. I hope i have explained it properly. Thanks a lot 

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

       

      Hi jimpatel ,

      You can update the formula of measure [PerMonthPercentage] as below to get it:

      PerMonthPercentage =
VAR _date =
    SELECTEDVALUE ( 'TABLE1'[Date] )
VAR StartDate =
    EOMONTH ( _date, -1 ) + 1
VAR EndDate =
    EOMONTH ( _date, 0 )
VAR CountWithCondition =
    CALCULATE (
        COUNT ( 'TABLE1'[Date] ),
        FILTER (
            ALLSELECTED ( 'TABLE1' ),
            'TABLE1'[Date] >= StartDate
                && 'TABLE1'[Date] <= EndDate
                && 'TABLE1'[Time] = 2
        )
    )
VAR TotalCount =
    CALCULATE (
        COUNT ( 'TABLE1'[Date] ),
        FILTER (
            ALLSELECTED ( 'TABLE1' ),
            'TABLE1'[Date] >= StartDate
                && 'TABLE1'[Date] <= EndDate
        )
    )
RETURN
    IF (
        NOT ( ISBLANK ( CountWithCondition ) ),
        DIVIDE ( CountWithCondition, TotalCount, 0 )
    )

      Best Regards

       

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

        Much appreciated. For some reason it is showing blank data column when i use the same concept formula.