Mixed results with Pivot data for linked PowerPivot tables

Anything related to PowerPivot and DAX Formuale
sboni
Posts: 1
Joined: Fri Feb 08, 2019 7:57 am

Mixed results with Pivot data for linked PowerPivot tables

Postby sboni » Fri Feb 08, 2019 8:07 am

Hi all,

hope anyone can help me on this as I have already spent countless hours to solve the problem. I am a fairly newbie to PowerPivot. I have uploaded two tables to PowerPivot. The first table is showing sales per customer by period, the second table is budget/ forecast by customer by period.

I have a unique identifier consistening of customer, business unit, material, sales person in both tables. I have already linked those tables and created a Pivot Table. Strangly in the pivot table I don't see the linked sign (meaning the chain symbol). I want to create a Pivot table showing me the actual sales per month vs. the forecasted sales per month by customer and also create a function which is showing me the difference.

Problem is my results vary depending on which table (Sales or Forecast) I pull the customer into the rows. It even happens that although I have a customer in both tables I only get e.g. a number for the sales figure and not for the forecast (although the customer is also in the forecast table). I have no clue of what is wrong with my table. Unfortunately it is sensisitive information so I can't post an example.

Hope anyone can help me on this. Happy to provide further information to clarify if there are any open questions. Thanks for your suppport. By the way I am using Excel 2016.

Stefan

PhilC
Posts: 175
Joined: Tue Sep 09, 2014 8:13 am

Re: Mixed results with Pivot data for linked PowerPivot tables

Postby PhilC » Tue Feb 12, 2019 12:38 pm

Hi Stefan,

It is easier to provide specific help if you provide a file with the model you have in place. Mocking up data can take a bit of time but greatly improves the assistance people can give.

You have effectively linked two data tables. You need to create lookup tables and use those on the visuals you are presenting.

For each of the dimensions you want to use that are in common between the tables (ie you mention customer, business unit, material, sales person), use Power Query to create a table for each that contains all the distinct values from the sales and budget data tables.

Then link the appropriate fields from the data tables to each of the lookup tables you have created.

https://exceleratorbi.com.au/create-lookup-table-power-pivot/
https://exceleratorbi.com.au/multiple-data-tables-power-pivot/

I'd be surprised if you do not need a date table as well. Try this https://exceleratorbi.com.au/build-reusable-calendar-table-power-query/.



Cheers


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 0 guests