cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JenniferLin Frequent Visitor
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

Accepted Solutions
Community Support Team
Community Support Team

Re: Column to Group row values of another column

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
vik0810 Member
Member

Re: Column to Group row values of another column

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

mussaenda Senior Member
Senior Member

Re: Column to Group row values of another column

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

JenniferLin Frequent Visitor
Frequent Visitor

Re: Column to Group row values of another column

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.

Community Support Team
Community Support Team

Re: Column to Group row values of another column

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.

JenniferLin Frequent Visitor
Frequent Visitor

Re: Column to Group row values of another column

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

           ...               |           ...

Community Support Team
Community Support Team

Re: Column to Group row values of another column

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

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 6 members 3,723 guests
Please welcome our newest community members: