Advanced Lookup

[LasithaRE]

Need to calculate Sales Amount based on the following;
Table 1 - has Following fields (Month, Customer ID, Product ID and Monthly Volumes
Table 2 - has following fields (Quarter, Customer ID, Product ID and Price)
Table 3 - has following Fields (Customer ID, Customer Name)
Table 4 - has following Fields (Month, Quarter)

For a particular product i need to calculate sales amount by Volume in to Price (Quarterly Price need to get from table 2 from relevant Month).

 

[Ozeroth]

Hi there,

If we keep your table setup as is, if you create appropriate relationships between the tables, you can use those relationships to perform the "lookup".

I have uploaded an example in Excel & Power BI Desktop.

I have renamed your tables just to make each table's purpose clearer:

Table 1 => Sales
Table 2 => Price
Table 3 => Customer
Table 4 => Calendar
Additional tables I added: Product & Quarter

Relationships required:

1. Sales[Month] - Calendar[Month]
2. Sales[Customer ID] - Customer[Customer ID]
3. Sales[Product ID] - Product[Product ID]
4. Price[Quarter] - Quarter[Quarter]
5. Price[Customer ID] - Customer[Customer ID]
6. Price[Product ID] - Product[Product ID]
7. Calendar[Quarter] - Quarter[Quarter]

First define Sales Volume:

[B]Sales Volume[/B] = 
SUM ( Sales[Volume] )

For Sales Amount, well, there are two ways I can think of to write it, either by iterating over Sales (summarized) or iterating over Price:

[B]Sales Amount Iterate Sales[/B] = 
SUMX (
    SUMMARIZE (
        Sales,
        Quarter[Quarter],
        Customer[Customer ID],
        'Product'[Product ID]
    ),
    [Sales Volume] * CALCULATE( VALUES ( Price[Price] ) )
)

[B]Sales Amount Iterate Price[/B] = 
SUMX (
    Price,
    [Sales Volume] *  Price[Price] 
)

The "lookup" happens due to the CALCULATE function being called within the row context of the SUMMARIZEd Sales table or Price, which adds the row context to the filter context. If you are iterating SUMMARIZE(Sales...), all of the columns filter Price. If you are iterating Price, the extended Price table (following all relationships from the Price table in the many-one direction) filters Sales.

Then Average Price if you want it is simply:

[B]
Average Price[/B] = 
DIVIDE ( [Sales Amount [I]whichever version[/I]], [Sales Volume] )

One change I would suggest is changing the Price table to Monthly instead of Quarterly (at the data load stage). This would allow you to get rid of the pesky Quarter table. Also, you might want to use a regular Date table instead of the monthly one (if it makes sense).
You could also pre-compute Sales Amount in the Sales table using Power Query.

Owen

 

[LasithaRE]

Hi Owen,

Thank you so much for the suggested solution. Anyway I kind of figured it out by myself using the proper relationships and Lookup DAX like you said. My works are attached herewith. >>> https://www.dropbox.com/s/9ezpe91qxcspwrq/3X3 Test.xlsx?dl=0

BTW I noticed that you are working for Fonterra and you know what I'm working for fonterra as well in Sri Lanka and I came across this issue when trying to develop a file for upcoming budgets. And the reason I have to used the quarterly prices is because we get our commodity prices quarterly.

Thanks again for the reply and may be you can help with this new issue I have now. Its about converting a two dimensional table to a list table. and like in the attached file. >>> https://www.dropbox.com/s/hrjgmqfy1uy05a7/VolumesMT Test.xlsm?dl=0

Thanks in Advanced

Lasitha
https://www.linkedin.com/in/lasitha-randula-elvitigala-1b151033/

 

[LasithaRE]

I can do this using power query right???

 

[Ozeroth]

Good to meet you Lasitha

Yes, I would recommend Power Query.

Load the original table to Power Query, Promote Headers if needed to make Customer ID, SKU, Month names are headers.
Then select the first two columns and "Unpivot Other Columns" (right-click or on the Transform tab).

All the best with that!
Owen