Forum Discussion
Summarizing & reworking Data
Hi all,
I have a table with entries for different products (with unique ID) over different years. In each row I have ID, Year and two different ages. Currently I am linking the two ages with two support tables where an age is linked with and age band: e.g. 0-10 years, 10-20 years, etc. so I can represent the data by age band without computing the relevant age band within the database. However, if I want to plot/compare the two different ages (by age band) side-by side it's impossible as I would have two different "x axis/legends".
Is there a way to summarize Ages by age band in a new table (considering that I would like to keep the possibility to choose the relevant year?) Thanks!
A Sample File here with a limited set of data. The result I'd like to achieve is in the "result" sheet: I downloaded aggregated data, put in excel and loaded back in PBI to achieve this. I'd simply like to have an automated way - retaining the possibility to switch between years - to compute it in PBI.
Hi Anonymous ,
You can do this without the need for the Ageband table.
On your Database unpivot your two Age columns this will give you two columns one witht the Attribute and value.
Create a new table with the age band you need:
AgeBandMinimumMaximum
0-10 0 9,99 10-20 10 19,99 20-30 20 29,99 30-40 30 39,99 +40 40 9999 Now create the following measure:
Count by age = VAR temnp_table = FILTER ( DataBase_Unpivot, DataBase_Unpivot[Value] <= MAX ( Ageband[Maximum] ) && DataBase_Unpivot[Value] >= MIN ( Ageband[Minimum] ) ) RETURN COUNTROWS ( temnp_table )If the Unpivot of the columns is not an option beside the age band table create one for the ages:
Ages
Age1 Age2
Now create the followin measure:
Count by age no unpivot = SWITCH ( SELECTEDVALUE ( Ages[Ages] ), "Age1", COUNTROWS ( FILTER ( DataBase, Database[Age] <= MAX ( Ageband[Maximum] ) && Database[Age] >= MIN ( Ageband[Minimum] ) ) ), "Age2", COUNTROWS ( FILTER ( DataBase, Database[Age 2] <= MAX ( Ageband[Maximum] ) && Database[Age 2] >= MIN ( Ageband[Minimum] ) ) ) )Check PBIX file with both options
I have kept your model for comparision but has refered no need to keep the age bands for both ages, also be aware that the two tables are not related with the other tables.
3 Replies
Hi Anonymous ,
You can do this without the need for the Ageband table.
On your Database unpivot your two Age columns this will give you two columns one witht the Attribute and value.
Create a new table with the age band you need:
AgeBandMinimumMaximum
0-10 0 9,99 10-20 10 19,99 20-30 20 29,99 30-40 30 39,99 +40 40 9999 Now create the following measure:
Count by age = VAR temnp_table = FILTER ( DataBase_Unpivot, DataBase_Unpivot[Value] <= MAX ( Ageband[Maximum] ) && DataBase_Unpivot[Value] >= MIN ( Ageband[Minimum] ) ) RETURN COUNTROWS ( temnp_table )If the Unpivot of the columns is not an option beside the age band table create one for the ages:
Ages
Age1 Age2
Now create the followin measure:
Count by age no unpivot = SWITCH ( SELECTEDVALUE ( Ages[Ages] ), "Age1", COUNTROWS ( FILTER ( DataBase, Database[Age] <= MAX ( Ageband[Maximum] ) && Database[Age] >= MIN ( Ageband[Minimum] ) ) ), "Age2", COUNTROWS ( FILTER ( DataBase, Database[Age 2] <= MAX ( Ageband[Maximum] ) && Database[Age 2] >= MIN ( Ageband[Minimum] ) ) ) )Check PBIX file with both options
I have kept your model for comparision but has refered no need to keep the age bands for both ages, also be aware that the two tables are not related with the other tables.
Hi, Anonymous
It seems that your data is from excel. If so ,you can also directly create such result table in PQ .
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZNBroMwDETvwrpCJHbs+CxV73+NQtyQGfRXf2WhB3kZY7/fW9leWz2Kn6XEPp5s+7xu0EdJ4AjiKiVBB1CPq9QEMUBdDrW9zQLosqgnckaXR3uiTmiYNBLFDwm4dFzidgm4WiJnFPl6QZeAyxNNl1IuQ5dSLkOXUi5Dl1IuQ1dbLnn0sC2XPHrYlksePWzLJY8eGrh0D3QZuFoiZxR5w0CXgcsTTZeDq3EuB5dxrv991ekryhWAfJc/UM8eCh4YEDkSdUL5K49EM3I5QNZ5bibreSQNzmSRZ9Lk/Fj6Co9OgR1X2Sv5YM3Pfankg00/F6aSD5b9PKzevs8X", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Snapshot Year" = _t, Age = _t, #"Age 2" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Snapshot Year", Int64.Type}, {"Age", type number}, {"Age 2", type number}}), #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Age Band", each if [Age] < 10 then "0-10" else if [Age] < 20 then "10-20" else if [Age] < 30 then "20-30" else if [Age] < 40 then "30-40" else if [Age] >= 40 then "40+" else null), #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Age Band2", each if [Age 2] < 10 then "0-10" else if [Age 2] < 20 then "10-20" else if [Age 2] < 30 then "20-30" else if [Age 2] < 40 then "30-40" else if [Age 2] >= 40 then "40+" else null), #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{ "Age", "Age 2"}), #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Age Band", "Age1"},{"Age Band2", "Age2"}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns1", {"ID", "Snapshot Year"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Age"}}), #"Grouped Rows" = Table.Group(#"Renamed Columns", {"Age", "Value", "Snapshot Year"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}}), #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Value]), "Value", "Count", List.Sum), #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Snapshot Year", Order.Ascending}, {"Age", Order.Ascending}}) in #"Sorted Rows"Please check my pbix file for more details.
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thank you both MFelix & v-easonf-msft .
I had to use the second solution from MFelix as the actual data are coming from a DAX code. The PQ code is really interesting too, thanks to v-easonf-msft
