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

[RyanTay]

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.

[parry2k · Accepted 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.⚡

[RyanTay]

Many Thanks for your help!

I get what I want!

[parry2k]

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

[RyanTay]

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.

[RyanTay]

Hi Parry2k, I hope you received my testing file and .pbix.

Able to solve it? Thanks for your help. 

[parry2k]

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

[RyanTay]

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

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

[RyanTay]

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