topic Re: Combing data from two resources based on %distribution in App Development

Combing data from two resources based on %distribution

AB108 — Wed, 03 May 2023 22:03:21 GMT

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.

Re: Combing data from two resources based on %distribution

edwin — Fri, 05 May 2023 13:06:31 GMT

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.

Re: Combing data from two resources based on %distribution

AB108 — Mon, 08 May 2023 16:24:23 GMT

@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.

Re: Combing data from two resources based on %distribution

Kushal_Chawda — Mon, 08 May 2023 23:19:10 GMT

@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)