Forum Discussion
Summary table from other tables
I've been working for hours to get a summary table that will look like this:
SummaryTable1
| Name |
Inventory | Available |
| Apple | 10 | 2 |
| Banana | 10 | 2 |
| Mango | 20 | 1 |
Name and Inventory column is hard coded. The values in Avalable column are from another table like this
Rawtable1
| Person | Apple | Banana | Mango |
| Mary | User | ||
| Paul | User | User | |
| Ben | User | ||
| Nel | User |
What should I formulate on the SummaryTable1 to get a summary view?
drthybl: You mentioned that your raw data table looked different than the raw data in the solution Ashish provided. However his solution starts with the same raw data that you provided.
You can see this by going into the Power Query Editor, via Transform Data on the Home section of the ribbon. Then in Power Query, if you click Table 2 in the Queries pane on the left, and then click Source in the Applied Steps pane on the right, you will see that the data is in your original format.Ashish then used the Unpivot Other Columns step to transform your raw data into the correct format needed to create a relationship to the other table.
Note that the Unpivot Other Columns step will still work with your non-simplified data that has many other columns.
Here's some more info on Unpivoting data: Unpivot columns (Power Query) (microsoft.com)
And a free course on Power Query Fundamentals: Power Query Fundamentals - Skillwave Training
He then created a relationship between the two tables, joining them on the name of the fruit.
More info: Create and manage relationships in Power BI Desktop - Power BI | Microsoft Docs
And then created two measures. One is just a sum, but the other, the "Available" measure, uses CALCULATE and DISTINCTCOUNT.
More info: CALCULATE – DAX Guide DISTINCTCOUNT - DAX Guide
It's a good solution and I think it would do you well to dive into it and understand it.
-Steve
4 Replies
However, my raw table looked like the raw table I have displayed above. please take note that my original raw table contains too many columns i just simplified it for better view 🙂
drthybl: You mentioned that your raw data table looked different than the raw data in the solution Ashish provided. However his solution starts with the same raw data that you provided.
You can see this by going into the Power Query Editor, via Transform Data on the Home section of the ribbon. Then in Power Query, if you click Table 2 in the Queries pane on the left, and then click Source in the Applied Steps pane on the right, you will see that the data is in your original format.Ashish then used the Unpivot Other Columns step to transform your raw data into the correct format needed to create a relationship to the other table.
Note that the Unpivot Other Columns step will still work with your non-simplified data that has many other columns.
Here's some more info on Unpivoting data: Unpivot columns (Power Query) (microsoft.com)
And a free course on Power Query Fundamentals: Power Query Fundamentals - Skillwave Training
He then created a relationship between the two tables, joining them on the name of the fruit.
More info: Create and manage relationships in Power BI Desktop - Power BI | Microsoft Docs
And then created two measures. One is just a sum, but the other, the "Available" measure, uses CALCULATE and DISTINCTCOUNT.
More info: CALCULATE – DAX Guide DISTINCTCOUNT - DAX Guide
It's a good solution and I think it would do you well to dive into it and understand it.
-Steve
