Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi , I need some helps on the Power BI formula:
I have a table like below:
Month | ToolNo | PValue |
Jan-21 | A10 | 1.15 |
Jan-21 | B26 | 1.23 |
Jan-21 | M32 | 1.06 |
Feb-21 | K34 | 1.51 |
Feb-21 | R85 | 1.48 |
I would like to find out the total number of tools in Jan-21; with PValue falls between 1.10 to 1.50
Note: ToolNo is string.
The result I expected is 2;
which is the total tool count of A10 and B26 (in Jan-21).
Please help. Thank you.
Solved! Go to Solution.
@RyanTay hey just sent you the email back, here is the measure.
toolcount new =
COUNTX ( VALUES ( 'Table'[ToolNo] ), IF ( [Avg PValue] >= 1.1 && [Avg PValue] <= 1.5, 1 ) )
Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@RyanTay hey just sent you the email back, here is the measure.
toolcount new =
COUNTX ( VALUES ( 'Table'[ToolNo] ), IF ( [Avg PValue] >= 1.1 && [Avg PValue] <= 1.5, 1 ) )
Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Many Thanks for your help!
I get what I want!
@RyanTay at this point it looks like we are not looking at things the same way. It will be easier if you share pbix file using one drive/google drive with the expected output. Remove any sensitive information before sharing.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Sorry Parry2k, I don't know how to share file thru onedrive.
I just emailed the testing file and .pbix to your email -- parvinder@perytus.com
Could you please help to check your mailbox?
Thanks.
Hi Parry2k, I hope you received my testing file and .pbix.
Able to solve it? Thanks for your help.
@RyanTay your original queston didn't have these details, if I understood correctly change the measure as below:
Count Tools =
CALCULATE ( DISTINCTCOUNT( Table[Tool] ), Table[PValue] >= 1.1, Table[PValue] <= 1.5 )
Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thanks for your quick response.
I still can't get the results. Seems like the Dinstictcount gets each line Pvalue (between 1.15 & 1.50) instead of Average Pvalue per Tool.
Sorry. Maybe I give one example.
ToolNo PValue
A10 1.15
A10 2.00
G12 1.33
A10 (Average Pvalue per tool) = (1.15+2.00)/2 = 1.58
Its average Pvalue fall outside the range of 1.15-1.50
So results I wish to get = 1; only G12 should be counted.
If use your Disticntcount formula, I will get 2; it counted A10-1.15 and G12-1.33
Any idea could be formulated as per above scenerio?
Many thanks!
@RyanTay add the following measure
Count Tools =
CALCULATE ( COUNTROWS ( Table ), Table[PValue] >= 1.1, Table[PValue] <= 1.5 )
In a table visual, add Month and Above measure and you will get the count.
Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thanks Parry2k.
I get similar results but I face one problem because I have repeated ToolNo with different PValue.
For example: A10 is repeated.
Month ___ ToolNO ___ PValue
Jan-11 ___ A10 ___ 1.15
Jan-11 ___ A10 ___ 1.50
By using your formula, I get total count of 3.
Countrows counted A10 as 2 lines.
I expect to get result of 2 only based on average Pvalue per tool.
After Averaging per tool, A10 average Pvalue is 1.33 (still within the range of 1.10-1.50).
But it should count as 1 only instead of 2 rows.
Please help. Thanks.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |