Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
hiren89vora
Frequent Visitor

Count with criteria and slicer selection

Hi All,

 

I want to get the count of people less than 50 for each date based on the criteria as filter option.

 

If i select criteria 1,2 &3 then i need the count of "id" that has sum of total less than "50"

 

Output : 

 

Datetotal<=50
01-Jan-213
07-Jan-212
14-Jan-212

 

idDateCriteriaTotal
a01-01-20211110
a07-01-20211110
a14-01-20211101
b01-01-2021245
b01-01-2021345
b07-01-2021245
b07-01-2021345
b14-01-2021245
b14-01-2021345
c01-01-2021472
c01-01-202112.5
c07-01-2021440
c07-01-2021320
c07-01-2021210
c07-01-202112.5
c14-01-2021490
c14-01-202112.5
d01-01-2021145
d07-01-2021190
d14-01-2021190
e01-01-2021448.3
e01-01-2021118
e01-01-2021524.7
e07-01-2021453.3
e07-01-2021118
e07-01-2021524.7
e14-01-2021448.3
e14-01-2021118
e14-01-2021524.7

 

Need help

2 ACCEPTED SOLUTIONS
Jihwan_Kim
Super User
Super User

Hi, @hiren89vora 

Please check the below picture and the sample pbix file's link down below.

 

Picture3.png

 

ID count Total Less than 50 =
SUMX (
VALUES ( 'Table'[Date] ),
COUNTROWS (
FILTER ( VALUES ( 'Table'[id] ), CALCULATE ( SUM ( 'Table'[Total] ) ) <= 50 )
)
)
 
 
 

Hi, My name is Jihwan Kim.


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


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

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.


Go to My LinkedIn Page


View solution in original post

Fowmy
Super User
Super User

@hiren89vora 

You can use the following measure to get the desired results:

Measure = 
COUNTROWS(
    FILTER(
        ADDCOLUMNS(
            VALUES(Table1[id]),
            "Sum", CALCULATE(SUM(Table1[Total]))
        ),
        [Sum] <= 50 
    )
)

Fowmy_0-1621426876000.png

 

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

4 REPLIES 4
Fowmy
Super User
Super User

@hiren89vora 

You can use the following measure to get the desired results:

Measure = 
COUNTROWS(
    FILTER(
        ADDCOLUMNS(
            VALUES(Table1[id]),
            "Sum", CALCULATE(SUM(Table1[Total]))
        ),
        [Sum] <= 50 
    )
)

Fowmy_0-1621426876000.png

 

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Jihwan_Kim
Super User
Super User

Hi, @hiren89vora 

Please check the below picture and the sample pbix file's link down below.

 

Picture3.png

 

ID count Total Less than 50 =
SUMX (
VALUES ( 'Table'[Date] ),
COUNTROWS (
FILTER ( VALUES ( 'Table'[id] ), CALCULATE ( SUM ( 'Table'[Total] ) ) <= 50 )
)
)
 
 
 

Hi, My name is Jihwan Kim.


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


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

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.


Go to My LinkedIn Page


amitchandak
Super User
Super User

@hiren89vora , based on what I got. Please try a measure like

sumx(filter(summarize(Table, Table[Date], "_1", sum(Table[Total])),[_1]<=50),[_1])

@amitchandak thanks for the response but i got blank as output

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.