Summary table from other tables

[drthybl]

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? 

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[drthybl]

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 🙂

[Ashish_Mathur]

Hi,

@SteveHailey has answered your question.

[SteveHailey · Accepted solution]

@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