Percentage calculation for each months

[jimpatel]

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 · Accepted solution]

@jimpatel 

You can download it from here : https://tmpfiles.org/9603907/sample.pbix

[jimpatel]

Yup. True. Strange. Any idea please?

[SachinNandanwar]

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]

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]

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]

Much appreciated again. I am getting below error. Any idea please?

Thanks a lot

[SachinNandanwar]

Try this.Probably there is a white space issue 

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

[jimpatel]

Thanks for your reply. But still same error

Thanks a lot

[SachinNandanwar]

[jimpatel]

Hi,

Sorry for one more question, What should i modify to get last 4 months percentage please? That is it will rolling 4 months. If i select July, it will be average of last 4 months percentage please. Any idea ? Really appreciated

[jimpatel]

Much appreciated

Thanks a lot

[jimpatel]

Thanks a lot. Is it possible to attach the solution copy please?

thanks a lot

[SachinNandanwar · Accepted solution]

@jimpatel 

You can download it from here : https://tmpfiles.org/9603907/sample.pbix

[jimpatel]

Thanks a lot for your support. One last question, what should i modify to get last 4 months rolling figure please? That is if i select July 2024, it will be showing average of last 4 month data. Same for June and so on.

Much appreciated again

[SachinNandanwar · Accepted solution]

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]

This is very strange..It works ok for me

The only change is the column name compared to yesterdays solution where you didnt had this issue.

Book1[MonthName] is replaced by Book1[MonthYear]. Thats all.

[jimpatel]

Hi,

Sorry for one more question, What should i modify to get last 4 months percentage please? That is it will rolling 4 months. If i select July, it will be average of last 4 months percentage please. Any idea ? Really appreciated

[Jihwan_Kim]

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]

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]

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]

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