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
Dubfelix
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

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]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Items", each 1),
    #"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"

View solution in original post

10 REPLIES 10
Greg_Deckler
Super User
Super User

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"

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Items", each 1),
    #"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"

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

Hi @HotChilli @Greg_Deckler ,

 

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 

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

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?

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

@HotChilli @Greg_Deckler 

 

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

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

PER_IDYEARWEEK NumberCount Per WeekCount Once per WeekTYPE
1201141BIKE
120131CAR
119221CAR
119451BIKE
119551CAR
119651BIKE
119751CAR
119851BIKE
119951CAR
1191051BIKE
1191151CAR
1191251BIKE
1191351CAR
1191451BIKE
1191551CAR
1191651BIKE
1191751CAR
1191851BIKE
1191951CAR
220151CAR
220151BIKE
220251CAR
220251BIKE
220451CAR
220451BIKE
220551CAR
220551BIKE
220651CAR
220651BIKE
220751CAR
220851BIKE
220951CAR
2201051BIKE
2201151CAR
2201251BIKE
2201351CAR
2201451BIKE
2201551CAR
2201651BIKE
2201751CAR
2201851BIKE
2201951CAR
2202051BIKE
320141CAR
320141BIKE
320241CAR
320241BIKE
320341CAR
320341BIKE
320441CAR
320441BIKE
320541CAR
320541BIKE
320641CAR
320641BIKE
320741CAR
320741BIKE
320841CAR
320841BIKE
320941CAR
320941BIKE
3201041CAR
3201041BIKE
3201141CAR
3201141BIKE
3201241CAR
3201241BIKE
3201341CAR
3201341BIKE
3201441CAR
3201441BIKE
3201541CAR
3201541BIKE
3201641CAR
3201641BIKE
3201741CAR
3201741BIKE
3201841CAR
3201841BIKE
3201941CAR
3201941BIKE
3202041CAR
3202041BIKE
420151CAR
420151BIKE
420251CAR
420251BIKE
420451CAR
420451BIKE
420551CAR
420551BIKE
420651CAR
420651BIKE
420751CAR
420851BIKE
420951CAR
4201051BIKE
4201151CAR
4201251BIKE
4201351CAR
4201451BIKE
4201551CAR
4201651BIKE
4201751CAR
4201851BIKE
4201951CAR
4202051BIKE

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.

Top Solution Authors
Top Kudoed Authors