% of total in Table

[learner03]

I have a column in my table as below-   

group

1 to 5

6 to 10

11 to 20

1 to 5

11 to 20

1111

3333

4444

555

1 to 5

11 to 20

1 to 5

6 to 10

11 to 20

1 to 5

6 to 10

1 to 5

11 to 20

1 to 5

6 to 10

11 to 20

1111

3333

4444

555 I need the output as in a table group by 1 to 5, 6 to 10 and 11 to 20 and not take any other numbers from the group, count of lines the group is appearing in the table and then the percentage of each group with respect to total group lines. the problem is whatever measure I create when I use the measure in table it is giving output as 100% for all 3 groups, it is not dividing by total lines. I need the output as-       group Count lines % of total 1 to 5 7 7/17=41% 6 to 10 4 4/17=24% 11 to 20 6 6/17=35% total 17

[BIswajit_Das · Accepted solution]

Hello @learner03 Here's a possible solution to your query
This is a sample set

Now you can create a measure
i.e

Measure =

    CALCULATE(

        COUNT('Table'[Count lines]),

        ALLEXCEPT('Table','Table'[group])

    )

to get percentage of the content according to the filtered data
so
to get only "1 to 5" and others you can filter that out by this
First expand the group
i.e

then select the reqiured values.
then add the measure to the table

i.e

then expands the measure and select the show value as total percentage to get get your required result

[PhilipTreacy]

Hi @learner03 

Download example PBIX file

In Power Query use this code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlQoyVcwVYrViVYyAzENDcBsQ7C4EZSDUIMqDgRghjEQgBkmQABmmJqa4tdKmrUoaigxkoCLYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Group = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Text.Contains([Group], "to")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Group"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Count] / List.Sum(#"Grouped Rows"[Count]))
in
    #"Added Custom"

Regards

Phil

[learner03]

@PhilipTreacy  i can't do in power query because the group is a calculated column based on some if conditions from other columns.

[PhilipTreacy]

@learner03 

That would have been useful to know in the beginning! 🙂