count the occurrence of values in a column for a dynamically changing value range

[Maheshwari]

Example :If  I have values from 1to 100 like 1,1,5,8,3,3,7,8,1,2... in column. Need to count the occurence of values from 1 to 5, 5 to 10 and so on.(Say here...1 to 5 --> 8 (1,1,5,8,3,31,2), 5 to 10 -->3 (8,7,8)).How to set dynaimc variable using DAX or this could be done in powerquery?

[Ashish_Mathur · Accepted solution]

Hi,

The answer should be 7 and 3 (not 8 and 3).  You may download my PBI file from here.

Hope this helps.

[Maheshwari]

Thanks Everyone.... I tried the first one & will try remaining two... inbetween I tried static segmentation which works good for my need

[v-rzhou-msft · Accepted solution]

Hi @Maheshwari 

You can try my way as well. Add a Type column by conditional column in Power query editor and then show results in table visual by count function.

Click close&apply. Then build a table visual by Type column and count of value column.

You can download the pbix file from this link: File.pbix

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[mahoneypat]

This would be a good use of the group/bin feature in Power BI.

Use grouping and binning in Power BI Desktop - Power BI | Microsoft Docs

Regards,

Pat

[CNENFRNL]

@Maheshwari , the easiest solution lie in Excel, our oldie but goodie; FREQUENCY() is kinda of ready-to-eat for such a question.

Here's my verbose solution in PQ; I made the span variable.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY67EcAgDEN3oU4R/AEzi4/91wjnR5EGnfUs4czWre0nm3qJS8nE7Jij3mV/NDE9YIv9DtOSYApK9GWTnN3OaxIYBIQbBBYwY3J+FzplUo2cU/YH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}}),

    ds = List.Buffer(List.Sort(#"Changed Type"[Value], Order.Ascending)),
    span = 5,
    Grouped = List.Accumulate(
        ds,
        {{0, 0}},// {{upper boudary, counter}, {}, {}, ...}
        (s,c) => if c>s{0}{0} then {{Number.RoundUp(c/span)*span, s{0}{1}+1}} & List.RemoveFirstN(s) & {{c}} else List.RemoveLastN(s) & {s{s{0}{1}} & {c}}
    ),
    Expanded = Table.FromRecords(List.Transform(List.RemoveFirstN(Grouped), each [Lower Boundary=#"Upper Boundary"-span, Upper Boundary=Number.RoundUp(List.Max(_)/span)*span, Count=List.Count(_)]))
in
    Expanded

 

[Ashish_Mathur · Accepted solution]

Hi,

The answer should be 7 and 3 (not 8 and 3).  You may download my PBI file from here.

Hope this helps.