cancel
Showing results for
Did you mean:
arvindyadav Member

## How to find count of value is greater than 6 As well as less than 6 In Power BI Desktop.

Hi Team,

I need help regarding How to find the count of value is greater than 6 As well as less than 6 In Power BI Desktop.

Thanks,

Arvind

2 ACCEPTED SOLUTIONS

Accepted Solutions Super User

## Re: How to find count of value is greater than 6 As well as less than 6 In Power BI Desktop.

@arvindyadav  This is the sample data I've created as per your post.. Now created a Average Measure as below

`Test220M2 = AVERAGE(Test220Meth2[TQMobile])`

Then flagging >=6 Average values as 1 and others 0. Create a New Measure for that as below

`Test220M2Count = IF([Test220M2]>=6,1,0)`

If you use this directly on the Table visual, we are getting wrong Totals. So create an another final measure to handle that.

```Test220M2Total =
VAR _Total = SUMX(VALUES(Test220Meth2[Name]),[Test220M2Count])
RETURN IF(HASONEVALUE(Test220Meth2[Name]),[Test220M2Count],_Total)``` Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut ! Super User

## Re: How to find count of value is greater than 6 As well as less than 6 In Power BI Desktop.

Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !

13 REPLIES 13 Super User

## Re: How to find count of value is greater than 6 As well as less than 6 In Power BI Desktop.

@arvindyadav  Create two measures as below and use them in the Multi-row card visual

```Test220Greaterthan6 = CALCULATE(COUNT(Test220Count[Value]),Test220Count[Value]>6)

Test220Lessthan6 = CALCULATE(COUNT(Test220Count[Value]),Test220Count[Value]<6) ```  Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !

arvindyadav Member

## Re: How to find count of value is greater than 6 As well as less than 6 In Power BI Desktop.

Whereas I have used measure.

Step-1

Average TQ Mobile = AVERAGE(Table1[TQ Mobile])

Step-2
Count greater than= if([Average TQ Mobile]>=6,1,0)

it works but not gives the total value. In image where measure 3 is count greater than meausre
Please find attached image.  Super User

## Re: How to find count of value is greater than 6 As well as less than 6 In Power BI Desktop.

@arvindyadav  Ok, you are using that average measure against some field in the table visual and based on that it is doing average. Use that field as a base and try the formula using "HASONEVALUE" in your IF condition

Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !

arvindyadav Member

## Re: How to find count of value is greater than 6 As well as less than 6 In Power BI Desktop.

Can you please share the DAX of "HASONEVALUE"? Super User

## Re: How to find count of value is greater than 6 As well as less than 6 In Power BI Desktop.

`=IF(HASONEVALUE(DateTime[CalendarYear]),SUM(ResellerSales_USD[SalesAmount_USD])/CALCULATE(SUM(ResellerSales_USD[SalesAmount_USD]),DateTime[CalendarYear]=2007),BLANK())`

Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !

arvindyadav Member

## Re: How to find count of value is greater than 6 As well as less than 6 In Power BI Desktop.

Not work for me this DAX can you provide me specific dax I have tried dax

Measure = IF(HASONEVALUE(DimDate[Date].[Year]),AVERAGE(Table1[TQ Mobile])/CALCULATE(AVERAGE(Table1[TQ Mobile]),DimDate[Date]=2019),BLANK())

Thanks,
Arvind Super User

## Re: How to find count of value is greater than 6 As well as less than 6 In Power BI Desktop.

Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !

arvindyadav Member

## Re: How to find count of value is greater than 6 As well as less than 6 In Power BI Desktop.

I am not able to share the pbix file. because I have no permission to spread data.

But this is the format I want

 Name Avg TQ Mobile TQ>=6 A 5.8 0 b 6.5 1 c 6.2 1 d 3.02 0 e 8 1 f 6 1 g 4.3 0 h 6.3 1 i 2 0 j 1.2 0 Total 5 Super User

## Re: How to find count of value is greater than 6 As well as less than 6 In Power BI Desktop.

@arvindyadav  This is the sample data I've created as per your post.. Now created a Average Measure as below

`Test220M2 = AVERAGE(Test220Meth2[TQMobile])`

Then flagging >=6 Average values as 1 and others 0. Create a New Measure for that as below

`Test220M2Count = IF([Test220M2]>=6,1,0)`

If you use this directly on the Table visual, we are getting wrong Totals. So create an another final measure to handle that.

```Test220M2Total =
VAR _Total = SUMX(VALUES(Test220Meth2[Name]),[Test220M2Count])
RETURN IF(HASONEVALUE(Test220Meth2[Name]),[Test220M2Count],_Total)``` Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !