Forum Discussion
Consolidate / Aggregate data for then creating visuals
Hi there,
I am very new to PowerBI/DAX and was looking for some assistance.
I have an Orderitem table of say 1000 orderitems of which there are 300 orders (average 3.3 lines per order). I need to do analysis on orders not orderitems, therefore i need to be able to aggregate or consolidate the rows.
I have illustrated the before and required after below.
Many thanks
| OrderNumber | OrderItem | Weight | Volume | Quantity | Item Price | LinePrice | Description |
| 1211 | 1 | 120 | 0.23 | 10 | 1.99 | 19.9 | Some parts |
| 1211 | 2 | 100 | 0.25 | 11 | 1.99 | 21.89 | Some parts |
| 1211 | 3 | 130 | 0.23 | 15 | 2.99 | 44.85 | Some parts |
| 1211 | 4 | 140 | 0.21 | 18 | 3.99 | 71.82 | Some parts |
| 1211 | 5 | 150 | 0.63 | 20 | 4.99 | 99.8 | Some parts |
| Becomes the below: | |||||||
| OrderNumber | OrderItem | Weight | Volume | Quantity | Item Price | LinePrice | Description |
| 1211 | 5 | 640 | 1.55 | 74 | 15.95 | 258.26 | Some parts |
| Max of | Count Of | Sum Of | Sum Of | Sum Of | Sum Of | Sum Of | Max of |
Hi tig2801,
If the dataset table is the same as what the picture shows, you only need to add columns to a table visual directly. Please pay attention to that don’t select the Description column, instead, you should create a new measure following the DAX forluma below:
Description = LASTNONBLANK(Sheet1[Description ] ,1).
If you have any question, please feel free to ask.
Best regards,
Yuliana Gu
1 Reply
Hi tig2801,
If the dataset table is the same as what the picture shows, you only need to add columns to a table visual directly. Please pay attention to that don’t select the Description column, instead, you should create a new measure following the DAX forluma below:
Description = LASTNONBLANK(Sheet1[Description ] ,1).
If you have any question, please feel free to ask.
Best regards,
Yuliana Gu