cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
arvindyadav Member
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.

Please help me out with this problem.

 

Thanks,

Arvind

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
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..

 

image.png

 

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)

image.png

 

 



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

Proud to be a Datanaut !





Super User
Super User

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

@arvindyadav  Sorry, copy & paste mistake... Updated the post.... please check



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

Proud to be a Datanaut !





13 REPLIES 13
Super User
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) 

 

image.pngimage.png

 



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

Proud to be a Datanaut !





arvindyadav Member
Member

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

Hi @PattemManohar ,

 

I have used your formula but not getting the correct answer. 

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.average count.PNG
 
Super User
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
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
Super User

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

@arvindyadav  Something like this...

 

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

Reference : https://docs.microsoft.com/en-us/dax/hasonevalue-function-dax

 

 

 



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

Proud to be a Datanaut !





arvindyadav Member
Member

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

Hi @PattemManohar ,

 

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())
 
Please help me out.
 
Thanks,
Arvind
Super User
Super User

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

@arvindyadav  Please share your pbix file, that will be helpful to understand better.



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

Proud to be a Datanaut !





arvindyadav Member
Member

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

Hi @PattemManohar,

 

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

But this is the format I want

NameAvg TQ MobileTQ>=6
A5.80
b6.51
c6.21
d3.020
e81
f61
g4.30
h6.31
i20
j1.20
Total 5
Super User
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..

 

image.png

 

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)

image.png

 

 



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

Proud to be a Datanaut !