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. 

      • 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