cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Patron

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 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

2 ACCEPTED SOLUTIONS
Resolver III

@jimpatel

Resolver III

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=

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.

21 REPLIES 21
Post Patron

Yup. True. Strange. Any idea please?

Resolver III

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=

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

Post Patron

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

Resolver III

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=

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

Post Patron

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

Thanks a lot

Resolver III

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=

RETURN _Pct_Calc
Post Patron

Thanks a lot

Resolver III

Post Patron

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

Post Patron

Much appreciated

Thanks a lot

Post Patron

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

thanks a lot

Resolver III

@jimpatel

Post Patron

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

Resolver III

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=

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.

Resolver III

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.

Post Patron

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

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)``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Post Patron

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

Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Post Patron

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

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors