Measuring % of values in column over/under constant value

[jmdowns22]

I have a spreadsheet with columns of items/machinery (ie Auger, Belt, etc) and under a list of values from 0-9999. I want to determine how many in % to report are over/under the value '100'. like 60% of the time Auger is >100 etc. Im new and just struggling to get a function to count rows and display those to report.

They are in a table titled '2024' with each column title representing the machinery.

Thanks

[Anonymous]

Hi @jmdowns22 ,

Agree with Selva-Salimi , If you have columns for each item, then trying to achieve what you want is difficult. As Selva-Salimi said please unpivot your table in Power Query first.
May I ask if you have solved your problem so far? If it has been solved, please accept Selva-Salimi's reply as a solution if you think it will help you, and if it hasn't been solved yet, perhaps you would like to provide us with some sample data so that we can help you find a solution with more precision.

Best Regards,
Dino Tao

[Selva-Salimi]

Hi @jmdowns22 

you can write a measure as follows:

measure percentage :=

var _hundred = calculate (count (items) , filter ('2024', value > 100 ))

return

_hundred /countrows(2024)

 and then this measure in percentage format.

If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly. 

[jmdowns22]

I got an syntax error return message:

The syntax for '>' is incorrect. (DAX(var _hundred = calculate (count (items) , filter ('2024', value > 100 ))return_hundred /countrows(2024))).  

[Selva-Salimi]

Did you use the name of column which contain the values 0..9999 instead of "value" in this code?? 

[jmdowns22]

I have entered

measure percentage =

var _hundred = calculate (count ('2024'[Auger]) , filter ('2024'[Auger] > 100 ))

return

_hundred /countrows('2024')

and now get a return error of 

Too few arguments were passed to the FILTER function. The minimum argument count for the function is 2.

My apologies 

[Selva-Salimi]

you should write it as follows:

var _hundred = calculate (count ('2024'[Auger]) , filter ('2024', '2024'[Auger] > 100 ))

and the question is, why did you use just Auger? does your table has column for each item?!

[jmdowns22]

The syntax for 'return' is incorrect. (DAX(calculate (count ('2024'[Auger]) , filter ('2024', '2024'[Auger] > 100 ))return_hundred /countrows('2024'))).

[Selva-Salimi]

you need space between return and _hundred 🙂

[jmdowns22]

Yes it has a couple dozen items - im just unsure how to do this appropriately. I want to list them all?

[Selva-Salimi]

of course not!

you should select columns and unpivot your table in power Query editor and then use the recommended calculation.

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly.