Distributing invoice value against product batches

Anything related to PowerPivot and DAX Formuale
jayadev
Posts: 1
Joined: Tue Jun 05, 2018 3:13 pm

Distributing invoice value against product batches

Postby jayadev » Wed Jun 06, 2018 3:21 pm

Hi,

Please help me to build a DAX formula to address the following issue, I am planning to use POWERPIVOT to do this work.
In the shared link, there are 3 sheets, 1. BlendSheet, 2.Inv, 3. Required output.

Blend sheet contain the product mix in various ratios. The objective is to generate a monthly sales report from the current month invoice. The invoice value should be allocated against in item in a batch based on the ratios given in the blend sheet.

Please help me solve this problem.

Please use this link.
https://docs.google.com/spreadsheets/d/ ... =388332244

Regards,
jay

andredl
Posts: 59
Joined: Wed Sep 23, 2015 9:56 pm

Re: Distributing invoice value against product batches

Postby andredl » Tue Jul 03, 2018 12:41 pm

Hi, I would recommend loading both the blend table and the invoices table into your data model, and then using Power Query to create a bridging table containing distinct values of the batch/blend numbers. This allows you to create relationships from both the batch table and the invoice table to the Batch/Blend lookup table. Hide those columns in the data tables so you don't accidentally use them in your pivot tables, as you should now only use the Batch/Blend values in the lookup table when you create pivot tables. Note that I created a blend% measure using the total mass as input, rather than the pre-calculated values you included in the table, as this is more robust.

Having those relationships allows the data to cross-filter when you multiply the quantities sold by the blend recipe.

The last step is to define the Qty distribution and Value distribution measures, where you need to use SUMX across the Blend table to get the totals you want.

Hope that's what you were after, please edit the heading of your post to include the word [SOLVED] if this has helped you solve your problem.

Cheers,
André
Attachments
Invoice Value Distribution.xlsx
(438.13 KiB) Downloaded 29 times


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 3 guests

cron