topic Re: Percentage calculation for each months in DAX Commands and Tips

Percentage calculation for each months

jimpatel — Thu, 18 Jul 2024 11:18:12 GMT

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

Re: Percentage calculation for each months

Jihwan_Kim — Thu, 18 Jul 2024 13:56:46 GMT

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)

Re: Percentage calculation for each months

SachinNandanwar — Thu, 18 Jul 2024 16:11:28 GMT

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

Re: Percentage calculation for each months

jimpatel — Thu, 18 Jul 2024 21:14:26 GMT

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

Re: Percentage calculation for each months

jimpatel — Thu, 18 Jul 2024 21:20:57 GMT

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

Re: Percentage calculation for each months

Anonymous — Fri, 19 Jul 2024 02:55:23 GMT

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

Re: Percentage calculation for each months

SachinNandanwar — Fri, 19 Jul 2024 06:59:58 GMT

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

Re: Percentage calculation for each months

jimpatel — Fri, 19 Jul 2024 07:28:00 GMT

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

Thanks a lot

Re: Percentage calculation for each months

jimpatel — Fri, 19 Jul 2024 07:28:36 GMT

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

Re: Percentage calculation for each months

Anonymous — Fri, 19 Jul 2024 08:01:15 GMT

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

Re: Percentage calculation for each months

SachinNandanwar — Fri, 19 Jul 2024 08:07:17 GMT

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

Re: Percentage calculation for each months

jimpatel — Fri, 19 Jul 2024 10:37:50 GMT

Thanks for your reply. But still same error

Thanks a lot

Re: Percentage calculation for each months

SachinNandanwar — Fri, 19 Jul 2024 11:03:29 GMT

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.

Re: Percentage calculation for each months

jimpatel — Fri, 19 Jul 2024 11:05:18 GMT

Yup. True. Strange. Any idea please?

Re: Percentage calculation for each months

SachinNandanwar — Fri, 19 Jul 2024 11:11:41 GMT

Re: Percentage calculation for each months

jimpatel — Mon, 22 Jul 2024 07:11:52 GMT

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

thanks a lot

Re: Percentage calculation for each months

SachinNandanwar — Mon, 22 Jul 2024 09:57:24 GMT

jimpatel

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

Re: Percentage calculation for each months

jimpatel — Mon, 22 Jul 2024 10:03:16 GMT

Much appreciated

Thanks a lot

Re: Percentage calculation for each months

jimpatel — Mon, 22 Jul 2024 10:47:49 GMT

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

Re: Percentage calculation for each months

jimpatel — Mon, 22 Jul 2024 14:49:17 GMT

Hi,