Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
DP2022
Frequent Visitor

Data with different columns but related information, trouble connecting and visualising

Hi folks

Wondering if anyone is able to assist me with this challenge. 

I have three sets of data in three different tabs. 

 

Tab 1: This a list of suppliers in one column (column A) and a further three columns (B,C and D) with different monetary values.


Tab 2: Responses from Y (this is less than 10) suppliers on their appetite for a list of 50 sites. This has a number of columns including site, location, region, values, supplier 1, supplier 2, supplier 3, etc. Under each supplier there are 50 rows with their appetite (Yes, No, Not Sure).

If this column structure is incorrect for the purposes of what I am trying to do, do let me know. For example where I need to have those 50 sites listed as 50 columns with Y rows denoting the appetite of those suppliers.


Tab 3: Another spreadsheet with a dozen suppliers on their appetite to perform an action (Yes, Yes with others, No) against a nature of work, against a region and against 4 different value bands. 

 

Tab 3 has the following columns:
Supplier Name (Supplier 1, Supplier 2, etc)
Appetite (Yes, Yes with others, No)
Region (Region 1, Region 2, Region 3, etc)
Value band (Band 1 <£m, Band 2 <£xm-ym, Band 3 <£xm-ym Band 4 >£XXm)
Nature of works (Type 1, Type 2, Type 3, Type 4)

 

Again, do let me know if Tab 3 needs to be structured differently i.e., having these column headers instead
Nature of Works (Type 1, Type 2, Type 3, Type 4)
Company Name (Supplier 1, Supplier 2, etc)
Region (Region 1, Region 2, Region 3, etc)
Value band: Band 1 <£m
Value band: Band 2 <£xm-ym
Value band: Band 3 <£xm-ym
Value band: 4 >£XXm

 

To recap Tab 3, for each supplier, there will be an appetite response to each of the four value bands for each region and for each nature of work.

 

As well as visualising their responses for in Tab 2 and Tab 3 so I can slide and dice it in various angles, I also want to be able to connect the two data sets and assess their appetite for sites against the value bands that they have selected for the region.
So in Tab 2, there is Site 1 located in Region 1 with a particular value, I want to see how that matches against their responses in Tab 3 where they have inputted their responses against a region and value band.

The last part of the challenge is to check various angles i.e., selecting nature of work or value bands or region / more than one regions to evaluate a suppliers appetite and to see how that impacts Tab 1 and the three columns of monetary values.

For example if I look against regions or against a nature of work or against a particular value band, they maybe fewer suppliers with appetite in a high value band or a region, I want to see how the monetary values change (in tab 1 if Supplier 1, 2 and 3 are remaining, I only want to see a total monetary value against those 3 suppliers and the monetary values against the other Suppliers who don't have appetite to be deducted.

 

Is this something that can be visualised in Excel or be more appropriate using Excel? The idea is to use Power BI and to be able to look at any angle quickly to see the impact on the monetary values in Tab 1.  And if further information is received, to be able to add that to the data set and see what impact that has. 

 

I hope I have explained this clearly. I would appreciate any assistance. 

 

Kind regards, D



1 REPLY 1
DP2022
Frequent Visitor

In case it helps, please see this in regard to the section on Tab 3:

 

Tab 3: Another spreadsheet with a dozen suppliers on their appetite to perform an action (Yes, Yes with others, No) against a nature of work, against a region and against 4 different value bands. 

 

Tab 3 has the following columns:
Supplier Name (Supplier 1, Supplier 2, etc)
Appetite (Yes, Yes with others, No)
Region (Region 1, Region 2, Region 3, etc)
Value band (Band 1 is 1,000,000 to 1,999,999, Band 2 is 2,000,000 to 2,999,999, Band 3 is 3,000,000 to 3,999,999 and Band 4 is 4,000,000 to 6,000,000.
Nature of works (Type 1, Type 2, Type 3, Type 4)

 

Again, do let me know if Tab 3 needs to be structured differently i.e., having these column headers instead:
Nature of Works (Type 1, Type 2, Type 3, Type 4)
Supplier Name (Supplier 1, Supplier 2, etc)
Region (Region 1, Region 2, Region 3, etc)
Value band: Band 1 is 1,000,000 to 1,999,999
Value band: Band 2 is 2,000,000 to 2,999,999
Value band: Band 3 is 3,000,000 to 3,999,999
Value band: Band 4 is 4,000,000 to 6,000,000.

For the last four column headers with the value band ranges, the cell entries would be a response on appetite such as Yes, No, or With Others.

To recap Tab 3, for each supplier, there will be an appetite response to each of the four value bands for each region and for each nature of work.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.