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
RyanTay
Frequent Visitor

Calculate total Tool count in certain month IF has specific values condition

Hi , I need some helps on the Power BI formula:

 

I have a table like below:

MonthToolNoPValue
Jan-21A101.15
Jan-21B261.23
Jan-21M321.06
Feb-21K341.51
Feb-21R851.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.

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

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

View solution in original post

9 REPLIES 9
parry2k
Super User
Super User

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

 

parry2k
Super User
Super User

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

parry2k
Super User
Super User

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

@parry2k 

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!

parry2k
Super User
Super User

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

@parry2k 

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.

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.