Create one unified dataset? Or Link separate datasets?

[DanielClyburn]

Hey Guys looking for a little guidance to hopefully not have to repeat my work multiple times to figure out the best way to set up my dataset.

Ok we get multple sales reports from different manufacturers that we represent (we represent 20 in total).  The reports contain the below columns (among others):

Customer Account Number, 2019 Sales, 2018 Sales

Note: all these manufacturers share a common customer base.  The Customer Account Number is assigned by the manufacturer, and each manufacturer gives different account numbers to the same customers)

Manufacturer A might send: 432345, $456, $420

Manufacturer B might send: H56, $8529, $6589

I have a secondary table that has columns for a master "Customer Name" and then columns for each manufactures part number.  Looks like:

Customer Name, ManuAccount#-A, ManuAccount#-B, ManuAccount#-C    etc. etc.

(example: Contoso CD, 432345, H56, xd456789)

I then crate a relationship thereby tagging the data in each manufacturers report an identical Customer Name.  so Both maufacturer A and manufacturer B data now are for "Contoso CD" customer.

Once I have the Customer Name assigned, I then have another table that gives me information about that customer, looks like:

Customer Name, Terriotry Sales Rep, Area, Address, Group 1

(example: Contoso CD, Bob Smith, North, Ferguson Group)

So I now can pull reports on each manufacturer that gives customer info, sales rep, area, ect.  Great!

However, the way we currently do this in Excel (the hard way, lol) we always end up with one large set of data with (among others) the below important columns:

"Manufacturers"

"Customer"

"Sales Rep"

"2018 Sales"

"2019 Sales"

I create a pivot table and can easily set the rows as "Sales Rep" then under that put "Manufacturer" and then under that put "Customer" and the values as the 2018 sales and 2019 sales.  I then get a compact report showing each sales reps sales per manufacturer for both years. and they can expand out see it broken down per customer.

I cannot find a way to recreate this in PowerBI.  I can create visualizations for each manufacturer then use a slicer for Sales Rep or for Customer, but it does give me that satisfying veiw where ai ahve a compact veiw with all the data with subtotals, ect.

Below is a screenshot with randomized data and only a subset, but accurate in its formatting.  PowerBI is allowing me to do so many more things, but this compant style report is at the core of our weekly update meetings, and is very printable.  I'd love to recreate something like this as part of what I am building. 

So...I am trying to decide how I can link these separate data sets together so that i can pull a Matrix visualzation with similar results to the above. I have only brought in two manufacturers data so far "Char" and "Sloan" seen below.  Wholesale Acct Number and Wholesalers are where i link the relationships to pull in other data.

Is an option to do a Merge Query and actually get the "Customer Name" column added to Char Data and Sloan Data.  But I still don't know how to use that common Column to create the matrix.

OR

Is there a way to create a sort of "Master" table that uses the Query Editor to make one large table, putting together the data from my separate manufacturers tables.  With everything in one table the data will be formatted the way I am used to.

OR

Am I thinking about this like an excel guy? Just not knowing the super simple trick i need to get my data the way I need it?

I know this is a long post, sorry about that.

[amitchandak · Accepted solution]

You can append data in power BI

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

https://www.poweredsolutions.co/2019/04/09/combine-or-append-data-in-power-bi-power-query-main-conce...

Summarize, Distinct, Addcoulumns along with union can be used to create dimension. Other than that edit query/transform data given you many options

https://exceltown.com/en/tutorials/power-bi/powerbi-com-and-power-bi-desktop/dax-query-language-for-...

https://www.sqlbi.com/articles/all-the-secrets-of-summarize/

distinct

https://docs.microsoft.com/en-us/dax/distinct-table-function-dax

https://community.powerbi.com/t5/Desktop/Getting-distinct-values-for-multiple-columns/td-p/31997

Transform data

https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data

Also , check

https://docs.microsoft.com/en-us/power-bi/guidance/

union (summarize(location1,location1[company],location1[Product],"Sales Location 1",sum(location1[sales]),"Sales Location 2",0.0),
summarize(location2,location1[company],location2[Product],"Sales Location 1",0.0,"Sales Location 2",sum(location2[sales]))
)
distinct(union(distinct(location1[company]),distinct(location2[company])))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin