Combing data from two resources based on %distribution

[AB108]

Hi,

Attaching sample data for this problem.

I am trying to combine Sales and Expense data. Sales data is straight forward. Expenses for each customer are calculated by different KPIs like for this sample it is percentage of their contribution to total sales from that particular site. This contribution has to be calculated in the dashboard based on various dynamic filters example date.

I am able to achieve this if I try to keep the site in the list box. Similarly if I try to filter on customer from a list box , the expense data goes blank.

Would appreciate some help regarding a more efficient way to script this.

[edwin]

is addresses your specific question:

load * inline [
Site,Customer,Sale Date,Sales
A,abc,Jan-23,200
A,bcd,Feb-23,100
A,cde,Mar-23,500
A,abc,Feb-23,800
A,bcd,Jan-23,100
A,cde,Mar-23,300
A,ghi,Mar-23,200
B,abc,Jan-23,500
B,bcd,Jan-23,900
B,cde,Feb-23,300
B,abc,Mar-23,200
B,bcd,Jan-23,600
B,cde,Jan-23,700
B,hij,Feb-23,900
];


load * inline [
Site,Expense Date,Labor,Maintenance,Fuel,Other,Total Expenses
A,Jan-23,50,10,20,10,90
A,Feb-23,60,10,20,10,100
A,Mar-23,70,10,20,10,110
B,Jan-23,80,10,20,10,120
B,Feb-23,90,10,20,10,130
B,Mar-23,100,10,20,10,140
];

note that i changed the names of the dates to prevent synth keys.  the solution really is to aggregate by site.

however, you may have a larger problem, you may want to allow the users to select months and analyze the perfromances by month.  that is a different discussion.

[AB108]

@edwin  Thanks for the reply.

The whole issue is that I want the users to analyze by month and a couple of other filters in the dashboard. That's what's causing the issue that I described.

[Kushal_Chawda]

@AB108  Create a composite Key between both the tables. Consider Sales table as your driving table as it is actual transaction tale. Create master calendar using Sales Date and also use Sales Site as Site Dimension

load Site ,
    Customer, 
    Date ,
    Sales, 
    Site&floor(Date#(Date,'MMM-YY')) as key 
inline [
Site,Customer, Date,Sales
A,abc,Jan-23,200
A,bcd,Feb-23,100
A,cde,Mar-23,500
A,abc,Feb-23,800
A,bcd,Jan-23,100
A,cde,Mar-23,300
A,ghi,Mar-23,200
B,abc,Jan-23,500
B,bcd,Jan-23,900
B,cde,Feb-23,300
B,abc,Mar-23,200
B,bcd,Jan-23,600
B,cde,Jan-23,700
B,hij,Feb-23,900
];


load Site as Expense_Site,
     Date as Expense_Date,
     Labor,
     Site&floor(Date#(Date,'MMM-YY')) as key ,
     Maintenance,
     Fuel,
     Other,
     [Total Expenses] 
inline [
Site,Date,Labor,Maintenance,Fuel,Other,Total Expenses
A,Jan-23,50,10,20,10,90
A,Feb-23,60,10,20,10,100
A,Mar-23,70,10,20,10,110
B,Jan-23,80,10,20,10,120
B,Feb-23,90,10,20,10,130
B,Mar-23,100,10,20,10,140
];

Autonumber Key;

Now create a table with below dimension and measure

Dimensions:
Site
Customer

Expressions:
1) Sales: 
=Sum(Sales)

2) Expenses: 
=sum(aggr((sum({<Customer=>}Sales)/ Sum(total <Site>{<Customer=>}Sales))*Sum(total <Site>{<Customer=>}[Total Expenses]),
Site,Customer))
Note: If you add more dimensions to your table, you need to exclude those dimensions in all set expressions and also add it to aggr functions

3) Column(1)-Column(2) 
// Column(1)-Sales Measure Number (1st Measure)
// Column(2)-Expense Measure Number (2nd Measure)