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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Shadaiv
Helper II
Helper II

Merge excel data with Data warehouse

Hi all,

Need your help here. 

We have our data warehouse model for Sales that includes two dimensions and Sales Fact. This is the master data for all the sales.

 

There is also an online portal which stores sales data in the form excel. 

We want to compare the number of sales are coming from Portal vs. all the sales captured in data warehouse.

The insights are:

1. How many sales are coming from Portal against all the sales

2.  sales by Product category (all sales and Portal Sales)

 

How should i link the excel data(which is one flat table) with our data warehouse model to get the expected insights

 

Shadaiv_0-1659424039830.png

 

 

Thanks

1 ACCEPTED SOLUTION

Hi @Shadaiv ,

 

I think you can try to connect "Portal" table with "Dim_Date" and "Dim_Product". Here I create a sample to have a test.

RicoZhou_0-1659685928989.png

Result is as below.

RicoZhou_1-1659685990140.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@Shadaiv , Join them with common dimension and analyze

 

You can have one table(enter data) with values(rows)

Portal

Store

 

In warehouse table add static column

Type = "Store"

 

In portal table add static column

Type = "Portal"

 

Join this new columns with new table and analyze

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Hi @amitchandak 
Thank you so much for your reply. 
I followed all the steps as you advised. When i try to show the trend line to see how many sales are in Dynamics and how many are coming through Portal, it does not provide the expected output. 

I am using Date from Dim_Date in X axis and for Y axis tusing the count of Sales ID from Fact_Sales (Dimensional model) and Count of sales from Portal data table (excel data)

Shadaiv_0-1659508658605.png

 

Please advice how can we ahieve the expected output. 

Many thanks.

Hi @Shadaiv ,

 

I think you can try to connect "Portal" table with "Dim_Date" and "Dim_Product". Here I create a sample to have a test.

RicoZhou_0-1659685928989.png

Result is as below.

RicoZhou_1-1659685990140.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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