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.

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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi jimpatel ,

    It is important to note that I am creating a measure, not a calculated column. As you mentioned that all the values returned are blank, could you please provide your sample PBIX file? We can then proceed with troubleshooting and provide you with an appropriate solution.

    Best Regards