Forum Discussion

Snehal_123's avatar
Snehal_123
Frequent Visitor
3 years ago

Measure to count people scoring below 50 %

Hi,

I want a solution for the following scenario.
I have a table with names and percentages.
I want a power query to count the people who have scored 70% to 80%

Thank you
Power query 

 

 

 

 

Need Help
Tips and Tricks

5 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    3 years ago

    Replace Source{Percentage] appropriately

    =List.Count(List.Select(Source[Percentage], (x)=>x>=0.7 and x<=0.8))
    • Snehal_123's avatar
      Snehal_123
      Frequent Visitor
      3 years ago

      Input:

      NamePercentage
      A50.00%
      B23.00%
      C54.00%
      A62.00%
      B78.00%
      C93.00%
      A90.00%
      B34.00%
      C19.00%
      A44.00%
      B89.00%
      C77.00%
      A34.00%


      Desired Output:

      Below 50%6
      51% to 80%4
      Above 80%3
      • Vijay_A_Verma's avatar
        Vijay_A_Verma
        Most Valuable Professional
        3 years ago

        Use this code

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TczBDcAgDEPRXXxGFSUpIceWMSL2X6MiXHz88pMj8KKgXk/FKoEvo0nGPItmHNYbMRvEXIg5v4kSu52YKrHhxMyI7YP1Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Percentage = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Percentage", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Category", each if [Percentage] <= 0.5 then "Below 50%" else if [Percentage] <= 0.8 then "51% to 80%" else "Above 80%"),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Category"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"
  • mukeshkasi25's avatar
    mukeshkasi25
    Frequent Visitor
    3 years ago

    Hi Snehal_123 

    We can create a measure to count number of peple scoring below the required condition.
    the syntax would be like:
    Measure= CALCULATE(COUNT(TABLE'PERCENTAGE'), FILTER(TABLE, TABLE(PERCENTAGE)<50%))