Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JenniferLin
Frequent Visitor

Column to Group row values of another column

I am new to Power BI.  I have tried several methods to solve my problem but I still could not figure out a proper way to display the data.

Any help will be appreciated, thanks.

 

 

There is a week number column in the dataset with row values 1 to 52 or 53. 

 

Week Num    |     Total Value

_______________________________

          1                 |          23

          2                 |          9

          3                 |          32

          ...                 |          ...

          52              |          64

          1                 |           12

          2                 |          73

          ...                 |          ...

          52               |          34

          53               |          48

          1                  |          94

          2                  |           106

          ...                  |           ...

          53                |          45

          ...                   |          ...

 

When I display the data with line or bar graph, the values add up, i.e. display Total Value = 258 for week number = 1.

However, I would like Total Value being displayed seperately.

My initial idea was to create a counter column which increases the index when the value of the week number is 1.

 

Week Num    |     Total Value   |  Counter

___________________________________________

          1                 |          23                  |           1

          2                 |          9                     |           1

          3                 |          32                  |           1

          ...                 |          ...                    |           1

          52              |          64                  |           1

          1                 |           12                 |           2

          2                 |          73                  |           2

          ...                 |          ...                     |           2

          52               |          34                 |           2

          53               |          48                 |           2

          1                  |          94                 |           3

          2                  |           106             |           3

          ...                  |           ...                   |           3

          53                |          45                 |           3

          1                  |           125              |           4  

          ...                   |          ...                    |           ...

 

I wrote a DAX function which throws an error message of "DAX comparison operations do not support comparing values of type True/False with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values."

Year Count =
VAR index = 1
VAR yearCount =
            IF(
                VALUE('Disruption Caused by Late Supply'[Week No.]) = 1,
                index = index++
                index = index)
RETURN index
 
Please help with fixing the problem of DAX function or identify any other alternative solutions.
I appreciate your helps a lot, thanks

 

1 ACCEPTED SOLUTION

Hi JenniferLin,

It should work, I get the result like below

404.PNG

You could refer to Numbering Grouped Data in Power Query for details.

Best Regards,
Zoe Zhi

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

 

View solution in original post

6 REPLIES 6
mussaenda
Super User
Super User

Its is because the power bi reads your same weeknum as one like grouping.

 

you need to add the year. Or make a year slicer

vik0810
Resolver V
Resolver V

Do you have a Year column in your dataset? If so, create a calculated column, like

 

YYYYWW = table[year] * 10000 + table[week num]

and use it as axis in your graph.

 

If this is not an option, you can add an index column in Power Query Editor.

 

queryoverview_queryview

 

LA8V4

Thank you so much for your reply.



There is no year column in the dataset.

I dont really understand how will adding an index column help solve the problem.  Could you explain it more? Thanks.

Hi JenniferLin, 

You could try below M code to add index to weeknumber 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Rc27CQAwDEPBXVSnkeVMY7L/GvmB3D1kDleBGGBgjUKc/CVX3vOr6e0SWdBCFiGTNGE/kQ0bpc3Z1gY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"week number" = _t, amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"week number", Int64.Type}, {"amount", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"week number"}, {{"all", each _, type table [week number=number, amount=number, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([all],"indexnew",1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"amount", "indexnew"}, {"Custom.amount", "Custom.indexnew"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"all"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Custom.indexnew", Order.Ascending}})
in
    #"Sorted Rows"

Best Regards,
Zoe Zhi

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

Hi @dax  thank you so much for the respond 🙂

 

I have tried your method but it does not seem to be what I want.

Instead of having,

Week Num   |   Counter

-----------------------------

            1             |           1

            2             |           1

            ...             |           1

            51           |           1

            52           |           1

            1             |           2

            2             |           2

            ...             |           2

            51           |           2

            1             |           3

            2             |           3

            ...             |           3

            51           |           3

           ...               |           ...

 

It displays

Week Num   |   Counter

-----------------------------

            1             |           1

            1             |           2

            1             |           4

            1             |           5

            1             |           3

            1             |           1

            1             |           2

            1             |           5

            1             |           3

            1             |           4

            1             |           1

            1             |           2

            1             |           3

            1             |           4

            1             |           5

           ...               |           ...

Hi JenniferLin,

It should work, I get the result like below

404.PNG

You could refer to Numbering Grouped Data in Power Query for details.

Best Regards,
Zoe Zhi

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.