Count with criteria and slicer selection

[hiren89vora]

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 : 

Date	total<=50
01-Jan-21	3
07-Jan-21	2
14-Jan-21	2

id	Date	Criteria	Total
a	01-01-2021	1	110
a	07-01-2021	1	110
a	14-01-2021	1	101
b	01-01-2021	2	45
b	01-01-2021	3	45
b	07-01-2021	2	45
b	07-01-2021	3	45
b	14-01-2021	2	45
b	14-01-2021	3	45
c	01-01-2021	4	72
c	01-01-2021	1	2.5
c	07-01-2021	4	40
c	07-01-2021	3	20
c	07-01-2021	2	10
c	07-01-2021	1	2.5
c	14-01-2021	4	90
c	14-01-2021	1	2.5
d	01-01-2021	1	45
d	07-01-2021	1	90
d	14-01-2021	1	90
e	01-01-2021	4	48.3
e	01-01-2021	1	18
e	01-01-2021	5	24.7
e	07-01-2021	4	53.3
e	07-01-2021	1	18
e	07-01-2021	5	24.7
e	14-01-2021	4	48.3
e	14-01-2021	1	18
e	14-01-2021	5	24.7

Need help

[Jihwan_Kim · Accepted solution]

Hi, @hiren89vora 

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

ID count Total Less than 50 =

SUMX (

VALUES ( 'Table'[Date] ),

COUNTROWS (

FILTER ( VALUES ( 'Table'[id] ), CALCULATE ( SUM ( 'Table'[Total] ) ) <= 50 )

)

)

https://www.dropbox.com/s/h9gcq96l8qe9yej/hiren.pbix?dl=0 

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

[Fowmy · Accepted solution]

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

[Jihwan_Kim · Accepted solution]

Hi, @hiren89vora 

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

ID count Total Less than 50 =

SUMX (

VALUES ( 'Table'[Date] ),

COUNTROWS (

FILTER ( VALUES ( 'Table'[id] ), CALCULATE ( SUM ( 'Table'[Total] ) ) <= 50 )

)

)

https://www.dropbox.com/s/h9gcq96l8qe9yej/hiren.pbix?dl=0 

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

[amitchandak]

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

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

[hiren89vora]

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