Home › Forums › Power Pivot › Trouble aggregating single column with multiple values
Tagged: Calculated Column, dax, Excel 2016
This topic contains 3 replies, has 2 voices, and was last updated by Steve Wheeler 7 years, 5 months ago.
-
AuthorPosts
-
October 3, 2016 at 1:14 am #6276
Hi! I’m brand new to the forum and just picked up Rob’s book. I’ve been exhausting all my options with this to no avail so thought I could send this out and hopefully get some help.
I’m having trouble finding a way to aggregate some data that was collected and entered into a column.
I have a list of transactions and users collected procedures that were listed on each account and entered them into a single column. Some accounts only have a single procedure listed while others could have as many as ten and they are separated by a comma.
I have a lookup table that refers to each procedure and I need to find a way to aggregate these. The data is arranged like the table below. Does anyone have any thoughts on a approach I could take?
Account # Date Procedure
6037147599 7/26/2016 J2357,96372,99211
6036013179 5/9/2016 J2357
6036318147 6/10/2016 J2357,93672
6037019927 6/10/2016 J2357,96372
6034426151 4/6/2016 J2357
6035148919 5/4/2016 J2357,96372
6035218060 5/12/2016 J2357,96372
6035739605 6/10/2016 J2357,96372
6023559987 3/3/2016 96372,95117,J2357October 3, 2016 at 3:59 am #6279Assuming the data above is in an Excel table, you could Split then Unpivot the Procedures with a Power Query such as below:
let
Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
#”Changed Type” = Table.TransformColumnTypes(Source,{{“Account #”, Int64.Type}, {” Date”, type date}, {“Procedure”, type text}}),
#”Split Column by Delimiter” = Table.SplitColumn(#”Changed Type”,”Procedure”,Splitter.SplitTextByDelimiter(“,”, QuoteStyle.Csv),{“Procedure.1”, “Procedure.2”, “Procedure.3″}),
#”Changed Type1″ = Table.TransformColumnTypes(#”Split Column by Delimiter”,{{“Procedure.1”, type text}, {“Procedure.2”, Int64.Type}, {“Procedure.3″, type text}}),
#”Unpivoted Other Columns” = Table.UnpivotOtherColumns(#”Changed Type1″, {“Account #”, ” Date”}, “Attribute”, “Value”),
#”Changed Type2″ = Table.TransformColumnTypes(#”Unpivoted Other Columns”,{{“Value”, type text}})
in
#”Changed Type2″That leaves a column for Attribute noting Procedure.1 through Procedure.3 but you can remove that if not needed.
October 3, 2016 at 11:29 pm #6298Thank you for the feedback Steve! I tried this approach and it worked well. I also found a great article that mentioned your approach as well as another and I’ve posted it here:
I’m trying now to aggregate the procedure data and I want to be able to sum the transaction amount associated with each Account #, but with the table now splitting and adding additional rows for each procedure it’s summing everything. I’ve attached a sample of the new table.
I’m unsure if I should be using SUMX or CALCULATE. Could I write a CALCULATE measure that only looks at distinct values?
Attachments:
You must be logged in to view attached files.October 4, 2016 at 3:20 am #6300That DataChant post DOES have an elegant and sustainable solution to this problem…!
On SUMming the Transaction Amount by (distinct) Account #, you COULD split out the Procedures to a separate table, but this forum post (sum column only for unique values in other column) seems to have some guidance using a MAXX measure within a SUMX measure.
I’m not sure how you’ll go though if you want to summarise by Account # AND PatID – good luck!
-
AuthorPosts
The forum ‘Power Pivot’ is closed to new topics and replies.