cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jayadev Helper I
Helper I

Distributing invoice value against batch

 

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.

 

Data shared through Google Sheets

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Distributing invoice value against batch

Hi @Jayadev

Do you put this formula in a calculate column?

You need select "create new table" and put this formula in.

 

Best Regards

Maggie

View solution in original post

7 REPLIES 7
Community Support
Community Support

Re: Distributing invoice value against batch

Hello @Jayadev

Sorry for not being able to help you currently since usage of DAX within PowerPivot  is different from that in Power BI Desktop.

Please turn to PowerPivot technical forum for better help.

 

Best Regards

Maggie

Jayadev Helper I
Helper I

Re: Distributing invoice value against batch

Could nout find active forum of Powerpivot.  Please share me the DAX in Powerbi.

 

Thanks in advance and request experts to help me.

Community Support
Community Support

Re: Distributing invoice value against batch

Hi @Jayadev

Would you like use some feature in query editor, as tested, I found it will be more simple when using DAX along with query editor in Power BI desktop.

1.In query editor, merge table “BlendSheet” and  “Inv”.

5.png

2.Then expand columns in Invoice table.

6.png

3.Create a new table using DAX in table BlendSheet in the Data view

Table1 =
SUMMARIZE (
    BlendSheet,
    [BLEND],
    [ESTATE],
    [GRADE],
    [bags],
    [net],
    [total],
    [%],
    [Batch],
    [Invoice.INVNO],
    [Invoice. VALUE ],
    "distrubution", [%] * [Invoice. VALUE ]
)

Here is my pbix for your reference.

 

Best Regards

Maggie

Jayadev Helper I
Helper I

Re: Distributing invoice value against batch

Thank you very much, appreciate a lot.

Will check and seek further help if needed.

 

Once again, Thanks.

Jayadev Helper I
Helper I

Re: Distributing invoice value against batch

I get this error, Please help

Batch Distribution.PNG

 

 

Community Support
Community Support

Re: Distributing invoice value against batch

Hi @Jayadev

Do you put this formula in a calculate column?

You need select "create new table" and put this formula in.

 

Best Regards

Maggie

View solution in original post

Jayadev Helper I
Helper I

Re: Distributing invoice value against batch

I think I made a mistake, rectified now and no error.

However, the distributed value is not matching.

Let me check once again.

 

Many many thanks for your support.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors