Filter and Count

[AllanBerces]

Hi can someone help me on my measure. I want to count the value on my table column day difference base from 4 criteria 

1). is lessthan 48

2). is equal to 72

3). is equal to 96

4). is greaterthan 96

The result should be equal to the total of this measure

Count S-08 =

SUMX(

VALUES('Index Month'[Month]),

CALCULATE(COUNTROWS(FILTER('Table', MONTH('Table'[S-08]) = SELECTEDVALUE('Index Month'[Month Index])))))

Desired Output

S-08	Day Difference S-08 -S-09/10
1/6/2026	0
1/23/2026	72
1/21/2026	144
1/20/2026	144
1/20/2026	144
1/20/2026	192
1/13/2026	24
1/15/2026	0
1/28/2026	0
1/26/2026	48
1/26/2026	48
1/21/2026	48
1/26/2026	48
1/14/2026	48
1/20/2026	48
1/23/2026	96
1/29/2026	96
1/5/2026	96
1/16/2026	264
1/29/2026	144
1/23/2026	96
1/5/2026	384
1/14/2026	144

[grazitti_sapna · Accepted solution]

Hi @AllanBerces,

You can achieve it by creating a calculated column first to define buckets and then create all the measures separately for each bucket and for count. also create a month column.

I've created a sample for you, Please take a look.

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[Ritaf1983 · Accepted solution]

Hi @AllanBerces 

You can do it, but the bucket logic needs to be fixed first.

The four conditions you listed do not cover all possible values, so they will not add up to your total measure. In your sample data, for example, there are several rows with a value of 48, and those are not included in any of your current buckets.

Right now your groups are:

less than 48
equal to 72
equal to 96
greater than 96

That leaves out values such as 48, and also anything between 49–71 or 73–95 if those exist in the data.

If you want the sum of all category measures to match your total, each row must belong to one and only one bucket.

For example, you could create measures like these:

Count S-08 < 48 =
SUMX(
VALUES('Index Month'[Month]),
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
MONTH('Table'[S-08]) = SELECTEDVALUE('Index Month'[Month Index]) &&
'Table'[Day Difference S-08 -S-09/10] < 48
)
)
)
Count S-08 = 48 =
SUMX(
VALUES('Index Month'[Month]),
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
MONTH('Table'[S-08]) = SELECTEDVALUE('Index Month'[Month Index]) &&
'Table'[Day Difference S-08 -S-09/10] = 48
)
)
)
Count S-08 = 72 =
SUMX(
VALUES('Index Month'[Month]),
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
MONTH('Table'[S-08]) = SELECTEDVALUE('Index Month'[Month Index]) &&
'Table'[Day Difference S-08 -S-09/10] = 72
)
)
)
Count S-08 = 96 =
SUMX(
VALUES('Index Month'[Month]),
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
MONTH('Table'[S-08]) = SELECTEDVALUE('Index Month'[Month Index]) &&
'Table'[Day Difference S-08 -S-09/10] = 96
)
)
)
Count S-08 > 96 =
SUMX(
VALUES('Index Month'[Month]),
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
MONTH('Table'[S-08]) = SELECTEDVALUE('Index Month'[Month Index]) &&
'Table'[Day Difference S-08 -S-09/10] > 96
)
)
)

A cleaner approach would be to create a bucket column first, then use that bucket in the visual instead of creating many separate measures.

Example:

S-08 Bucket =
SWITCH(
TRUE(),
'Table'[Day Difference S-08 -S-09/10] < 48, "<48",
'Table'[Day Difference S-08 -S-09/10] = 48, "48",
'Table'[Day Difference S-08 -S-09/10] = 72, "72",
'Table'[Day Difference S-08 -S-09/10] = 96, "96",
'Table'[Day Difference S-08 -S-09/10] > 96, ">96",
"Other"
)

Then you can simply count rows by that bucket.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[AllanBerces]

Hi @Ritaf1983 just follow up question it doesnot give the correct Total, how can i correct it

Thank you

[AllanBerces]

Hi @Ritaf1983 @grazitti_sapna thank you very much for the reply work perfectly

[grazitti_sapna]

@AllanBerces,

Kindly mark as solution if it worked for you

[grazitti_sapna · Accepted solution]

Hi @AllanBerces,

You can achieve it by creating a calculated column first to define buckets and then create all the measures separately for each bucket and for count. also create a month column.

I've created a sample for you, Please take a look.

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!