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 this instance, for month of feb it will be 2/6 which will be 33% and so on

 

 

I am using below code to get the result, but it is not working as i am expecting. Any idea please?

 

PerMonthPercentage = 
VAR StartDate = EOMONTH(SELECTEDVALUE('TABLE1'[Date]), -1) + 1
VAR EndDate = EOMONTH(SELECTEDVALUE('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)

 Any idea where i am going wrong please

  • 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.