Forum Discussion

Jayadev's avatar
Jayadev
Helper I
8 years ago
Solved

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...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    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

v-juanli-msft's avatar
v-juanli-msft
Community Support
8 years ago

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

2.Then expand columns in Invoice table.

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's avatar
Jayadev
Helper I
8 years ago

I get this error, Please help

 

 

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    8 years ago

    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

    • Jayadev's avatar
      Jayadev
      Helper I
      8 years ago

      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.