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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
arvindyadav
Post Partisan
Post Partisan

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

@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 PBI Community Champion




View solution in original post

@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 PBI Community Champion




View solution in original post

14 REPLIES 14
PattemManohar
Community Champion
Community Champion

@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 PBI Community Champion




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
 

@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 PBI Community Champion




Can you please share the DAX of "HASONEVALUE"?

@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 PBI Community Champion




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

@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 PBI Community Champion




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

@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 PBI Community Champion




Hi! 

 

Could you please explain to me what does the last measure means in "English"? Thank you very much

Hi @PattemManohar ,

 

Got it. But why you are creating the same measure 2 times i.e, Test220M2 = AVERAGE(Test220Meth2[TQMobile])

and how to find [Test220M2Count]?

 

Thanks,

Arvind

 

@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 PBI Community Champion




Hi @PattemManohar ,

 

Still getting an error like 

The function SUMX cannot work with values of type String.

 

Please find the average.PNGimage.

 

Hi @PattemManohar ,

 

Got the error thank you so much 🙂

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.