cancel
Showing results for
Did you mean:
Frequent Visitor

## Sum by Per ID and week number based on 1 column. column contains two outcomes.

Hi All,

I am trying to sum by Sale Item

example table below

Per ID Wk           Sale Item

1         20                    Bike

1         20                    Car

2         20                    Bike

3         21                    Bike

3         22                    Car

Just looking to get

Per ID Week      Bike  Car

1         20            1       1

2         20             1       0

3         21             1       0

3          22            0       1

Then Group by week

Wk   Bike & Car Car   Bike

20        1               0       1

21         0              0       1

22         0              1        0

I am interested in bringing back the zero's...Thanks

1 ACCEPTED SOLUTION
Super User II

You'll need to test this as I threw in some quick logic to get your Bike and Car column

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtUvB0UdJRCs8GEsGJOakKniWpuUqxOtFKhkARIwMg4ZSZnYoq4pxYBBYwwlBiDBIxxBAxgmmKBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
#"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[#"Sale Item"]), "Sale Item", "Items", List.Count),
#"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Per ID"}),
#"Inserted Column" = Table.AddColumn(#"Removed Columns2", "Bike an Car", each List.Min({[Bike], [Car]}), type number),
#"Grouped Rows" = Table.Group(#"Inserted Column", {"Wk"}, {{"Car", each List.Min([Car]), type number}, {"Bike", each List.Min([Bike]), type number}, {"Bike And Car", each List.Max([Bike an Car]), type number}})
in
#"Grouped Rows"``````
10 REPLIES 10
Super User IV

This is close. Had it right up until you threw in the extra column at the end.

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyABJOmdmpSrE6SCLOiUVgASMMJcYgEUMMESOYplgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Per ID" = _t, Wk = _t, #"Sale Item" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Per ID", Int64.Type}, {"Wk", Int64.Type}, {"Sale Item", type text}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[#"Sale Item"]), "Sale Item", "Per ID", List.Count)
in
#"Pivoted Column"``````

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

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Super User II

You'll need to test this as I threw in some quick logic to get your Bike and Car column

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtUvB0UdJRCs8GEsGJOakKniWpuUqxOtFKhkARIwMg4ZSZnYoq4pxYBBYwwlBiDBIxxBAxgmmKBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
#"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[#"Sale Item"]), "Sale Item", "Items", List.Count),
#"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Per ID"}),
#"Inserted Column" = Table.AddColumn(#"Removed Columns2", "Bike an Car", each List.Min({[Bike], [Car]}), type number),
#"Grouped Rows" = Table.Group(#"Inserted Column", {"Wk"}, {{"Car", each List.Min([Car]), type number}, {"Bike", each List.Min([Bike]), type number}, {"Bike And Car", each List.Max([Bike an Car]), type number}})
in
#"Grouped Rows"``````
Frequent Visitor

Thanks to both of you replying!  I will give it a spin today to see if I can get it working on my Data

Frequent Visitor

The logic is not working when I apply it to a large dataset, could you check the logic to see if there is a better way of doing it?

Thanks

Dubfelix

Super User II

Can you post a larger sample dataset please?  With some data that illustrates the issue.

Frequent Visitor

Will do I think the issue is it is only counting 1 per week for all customers and maybe it should be a sum of the count?

Frequent Visitor

Per ID Wk Sale Item

1 20 Bike

1 20 Car

2 20 Bike

3 21 Bike

3 22 Car

4 20 Bike

4 21 Bike

4 22 Car

4 22 Bike

5 20 Car

5 22 Bike

5 22 Car

6 20 Car

6 22 Bike

7 22 Car

7 21 Bike

8 21 Car

8 21 Bike

8 20 Car

8 20 Bike

8 22 Bike

Frequent Visitor

Just doubling the data will do...Any Suggestions would be great.

Frequent Visitor

PER_ID YEAR WEEK Number Count Per Week Count Once per Week TYPE
1 20 1 14 1 BIKE
1 20 1 3 1 CAR
1 19 2 2 1 CAR
1 19 4 5 1 BIKE
1 19 5 5 1 CAR
1 19 6 5 1 BIKE
1 19 7 5 1 CAR
1 19 8 5 1 BIKE
1 19 9 5 1 CAR
1 19 10 5 1 BIKE
1 19 11 5 1 CAR
1 19 12 5 1 BIKE
1 19 13 5 1 CAR
1 19 14 5 1 BIKE
1 19 15 5 1 CAR
1 19 16 5 1 BIKE
1 19 17 5 1 CAR
1 19 18 5 1 BIKE
1 19 19 5 1 CAR
2 20 1 5 1 CAR
2 20 1 5 1 BIKE
2 20 2 5 1 CAR
2 20 2 5 1 BIKE
2 20 4 5 1 CAR
2 20 4 5 1 BIKE
2 20 5 5 1 CAR
2 20 5 5 1 BIKE
2 20 6 5 1 CAR
2 20 6 5 1 BIKE
2 20 7 5 1 CAR
2 20 8 5 1 BIKE
2 20 9 5 1 CAR
2 20 10 5 1 BIKE
2 20 11 5 1 CAR
2 20 12 5 1 BIKE
2 20 13 5 1 CAR
2 20 14 5 1 BIKE
2 20 15 5 1 CAR
2 20 16 5 1 BIKE
2 20 17 5 1 CAR
2 20 18 5 1 BIKE
2 20 19 5 1 CAR
2 20 20 5 1 BIKE
3 20 1 4 1 CAR
3 20 1 4 1 BIKE
3 20 2 4 1 CAR
3 20 2 4 1 BIKE
3 20 3 4 1 CAR
3 20 3 4 1 BIKE
3 20 4 4 1 CAR
3 20 4 4 1 BIKE
3 20 5 4 1 CAR
3 20 5 4 1 BIKE
3 20 6 4 1 CAR
3 20 6 4 1 BIKE
3 20 7 4 1 CAR
3 20 7 4 1 BIKE
3 20 8 4 1 CAR
3 20 8 4 1 BIKE
3 20 9 4 1 CAR
3 20 9 4 1 BIKE
3 20 10 4 1 CAR
3 20 10 4 1 BIKE
3 20 11 4 1 CAR
3 20 11 4 1 BIKE
3 20 12 4 1 CAR
3 20 12 4 1 BIKE
3 20 13 4 1 CAR
3 20 13 4 1 BIKE
3 20 14 4 1 CAR
3 20 14 4 1 BIKE
3 20 15 4 1 CAR
3 20 15 4 1 BIKE
3 20 16 4 1 CAR
3 20 16 4 1 BIKE
3 20 17 4 1 CAR
3 20 17 4 1 BIKE
3 20 18 4 1 CAR
3 20 18 4 1 BIKE
3 20 19 4 1 CAR
3 20 19 4 1 BIKE
3 20 20 4 1 CAR
3 20 20 4 1 BIKE
4 20 1 5 1 CAR
4 20 1 5 1 BIKE
4 20 2 5 1 CAR
4 20 2 5 1 BIKE
4 20 4 5 1 CAR
4 20 4 5 1 BIKE
4 20 5 5 1 CAR
4 20 5 5 1 BIKE
4 20 6 5 1 CAR
4 20 6 5 1 BIKE
4 20 7 5 1 CAR
4 20 8 5 1 BIKE
4 20 9 5 1 CAR
4 20 10 5 1 BIKE
4 20 11 5 1 CAR
4 20 12 5 1 BIKE
4 20 13 5 1 CAR
4 20 14 5 1 BIKE
4 20 15 5 1 CAR
4 20 16 5 1 BIKE
4 20 17 5 1 CAR
4 20 18 5 1 BIKE
4 20 19 5 1 CAR
4 20 20 5 1 BIKE

Frequent Visitor
 PER_ID YEAR WEEK Number Count Per Week Count Once per Week TYPE 1 20 1 14 1 BIKE 1 20 1 3 1 CAR 1 19 2 2 1 CAR 1 19 4 5 1 BIKE 1 19 5 5 1 CAR 1 19 6 5 1 BIKE 1 19 7 5 1 CAR 1 19 8 5 1 BIKE 1 19 9 5 1 CAR 1 19 10 5 1 BIKE 1 19 11 5 1 CAR 1 19 12 5 1 BIKE 1 19 13 5 1 CAR 1 19 14 5 1 BIKE 1 19 15 5 1 CAR 1 19 16 5 1 BIKE 1 19 17 5 1 CAR 1 19 18 5 1 BIKE 1 19 19 5 1 CAR 2 20 1 5 1 CAR 2 20 1 5 1 BIKE 2 20 2 5 1 CAR 2 20 2 5 1 BIKE 2 20 4 5 1 CAR 2 20 4 5 1 BIKE 2 20 5 5 1 CAR 2 20 5 5 1 BIKE 2 20 6 5 1 CAR 2 20 6 5 1 BIKE 2 20 7 5 1 CAR 2 20 8 5 1 BIKE 2 20 9 5 1 CAR 2 20 10 5 1 BIKE 2 20 11 5 1 CAR 2 20 12 5 1 BIKE 2 20 13 5 1 CAR 2 20 14 5 1 BIKE 2 20 15 5 1 CAR 2 20 16 5 1 BIKE 2 20 17 5 1 CAR 2 20 18 5 1 BIKE 2 20 19 5 1 CAR 2 20 20 5 1 BIKE 3 20 1 4 1 CAR 3 20 1 4 1 BIKE 3 20 2 4 1 CAR 3 20 2 4 1 BIKE 3 20 3 4 1 CAR 3 20 3 4 1 BIKE 3 20 4 4 1 CAR 3 20 4 4 1 BIKE 3 20 5 4 1 CAR 3 20 5 4 1 BIKE 3 20 6 4 1 CAR 3 20 6 4 1 BIKE 3 20 7 4 1 CAR 3 20 7 4 1 BIKE 3 20 8 4 1 CAR 3 20 8 4 1 BIKE 3 20 9 4 1 CAR 3 20 9 4 1 BIKE 3 20 10 4 1 CAR 3 20 10 4 1 BIKE 3 20 11 4 1 CAR 3 20 11 4 1 BIKE 3 20 12 4 1 CAR 3 20 12 4 1 BIKE 3 20 13 4 1 CAR 3 20 13 4 1 BIKE 3 20 14 4 1 CAR 3 20 14 4 1 BIKE 3 20 15 4 1 CAR 3 20 15 4 1 BIKE 3 20 16 4 1 CAR 3 20 16 4 1 BIKE 3 20 17 4 1 CAR 3 20 17 4 1 BIKE 3 20 18 4 1 CAR 3 20 18 4 1 BIKE 3 20 19 4 1 CAR 3 20 19 4 1 BIKE 3 20 20 4 1 CAR 3 20 20 4 1 BIKE 4 20 1 5 1 CAR 4 20 1 5 1 BIKE 4 20 2 5 1 CAR 4 20 2 5 1 BIKE 4 20 4 5 1 CAR 4 20 4 5 1 BIKE 4 20 5 5 1 CAR 4 20 5 5 1 BIKE 4 20 6 5 1 CAR 4 20 6 5 1 BIKE 4 20 7 5 1 CAR 4 20 8 5 1 BIKE 4 20 9 5 1 CAR 4 20 10 5 1 BIKE 4 20 11 5 1 CAR 4 20 12 5 1 BIKE 4 20 13 5 1 CAR 4 20 14 5 1 BIKE 4 20 15 5 1 CAR 4 20 16 5 1 BIKE 4 20 17 5 1 CAR 4 20 18 5 1 BIKE 4 20 19 5 1 CAR 4 20 20 5 1 BIKE

Announcements