Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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."
Solved! Go to Solution.
Hi JenniferLin,
It should work, I get the result like below
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.
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
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.
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
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |