Forum Discussion
Post PatronPercentage 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
jimpatel
You can download it from here : https://tmpfiles.org/9603907/sample.pbixI 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 averagesRolling3MonthAverage = 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.
21 Replies
- Jihwan_Kim
Super User
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)
jimpatelThanks 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
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
- SachinNandanwar
Impactful Individual
This ?
Create a summary table using the following DAXCalc_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_Calcwhere 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
Yup. True. Strange. Any idea please?
