Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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)
Proud to be a PBI Community Champion
@arvindyadav Sorry, copy & paste mistake... Updated the post.... please check
Proud to be a PBI 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)
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
@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
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
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
@arvindyadav Please share your pbix file, that will be helpful to understand better.
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
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 |
@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)
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
Proud to be a PBI Community Champion
Hi @PattemManohar ,
Still getting an error like
Please find the image.
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |