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

[arvindyadav]

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

[PattemManohar · Accepted solution]

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

[PattemManohar]

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

[arvindyadav]

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.

[PattemManohar]

@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

[arvindyadav]

Can you please share the DAX of "HASONEVALUE"?

[PattemManohar]

@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

[arvindyadav]

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

[PattemManohar]

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

[arvindyadav]

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

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

[PattemManohar · Accepted solution]

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

[GARMAS97]

Hi! 

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

[arvindyadav]

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

[PattemManohar · Accepted solution]

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

[arvindyadav]

Hi @PattemManohar ,

Still getting an error like 

The function SUMX cannot work with values of type String.

Please find the image.

[arvindyadav]

Hi @PattemManohar ,

Got the error thank you so much 🙂