cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rajveer1117
Helper IV
Helper IV

Count where Value >160

 

Emp n	Emp Nam	Value
822	Abc	180
121	b	300
400	C	120
492	D	100
312	E	200
		
		
I want to filter data Value >160 & show the output as 3		

 

Need help in getting count where value is >160. I want to show this in tile.

 

Thanks,

Raj

1 ACCEPTED SOLUTION

Try this:

Measure =
VAR SummaryTable =
    SUMMARIZECOLUMNS(
        'Data 2'[Emp n],
        'Data 2'[Emp Nam],
        "Total Value", SUM( 'Data 2'[Value] )
    )
RETURN
    COUNTX(
        FILTER(
            SummaryTable,
            [Total Value] > 160
        ),
        [Emp n]
    )

 

If you need additional help please provide data in a valid table. See links below. Text boxes are not good ways to convey tabular data.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

4 REPLIES 4
edhans
Super User
Super User

 

COUNTX works over a table, so COUNTX(table,expression). FILTER() below is returning a table that only has the rows that are over 160, then COUNTX is just counting the employee names in that filtered table.

 

Measure = 
COUNTX(
    FILTER(
        Data,
        Data[Value] > 160
    ),
    Data[Emp n]
)

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Hi I need to be more specfic on the details.

Raw Table:

Date	Emp n	Emp Nam	Value		
1/1/2020	822	Abc	60		
2/1/2020	822	Abc	120		
1/1/2020	121	b	150		
2/1/2020	121	b	150		
1/1/2020	400	C	80		
1/2/2020	400	C	40		
1/3/2020	492	D	100		
1/4/2020	312	E	200		
	After removing the Date , the data is aggregated based on the mp name.				
					
	Emp n	Emp Nam	Value		
					
	822	Abc	180		
	121	b	300		
	400	C	120		
	492	D	100		
	312	E	200		
					
					
					
					
					
I want to filter data Value >160 & show the output as 3					

Hi,

Try these measure

Values = SUM(Data[Value])

Count = COUNTROWS(FILTER(SUMMARIZE(Values(Data[Emp n]),Data[Emp n],"ABCD",[Values]),[Values]>160))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Try this:

Measure =
VAR SummaryTable =
    SUMMARIZECOLUMNS(
        'Data 2'[Emp n],
        'Data 2'[Emp Nam],
        "Total Value", SUM( 'Data 2'[Value] )
    )
RETURN
    COUNTX(
        FILTER(
            SummaryTable,
            [Total Value] > 160
        ),
        [Emp n]
    )

 

If you need additional help please provide data in a valid table. See links below. Text boxes are not good ways to convey tabular data.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors