Forum Discussion

gauravnarchal's avatar
gauravnarchal
Post Prodigy
4 years ago

Need help with Calculated Column or Measure

Hello - I need your help in creating a measure or calculated column   Show all invoice numbers and total Amounts in columns against each (Settlement) Auth. When there are multiple invoice numbers ...
Need Help
Show and Tell
v-chenwuz-msft's avatar
v-chenwuz-msft
Community Support
4 years ago

Hi gauravnarchal 

 

The link to you pbix file has expired. So I create some data depend on your discripation.i put my pbix file in the end and you can reference.

Try this measure:

Measure =
CONCATENATEX( 'ARInvoices', [invoice number], "/" )

 

If you want to create a column the following formula will helpful.

 

Amount =
SUMX(
    FILTER( 'CashReceipt', [AUTH] = EARLIER( 'settlement'[AUTH] ) ),
    CashReceipt[Amount]
)
invoicenumber =
CONCATENATEX(
    FILTER( ARInvoices, ARInvoices[AUTH] = EARLIER( settlement[AUTH] ) ),
    [invoice number],
    "/"
)

 

Best Regards

Community Support Team _ chenwu zhu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • gauravnarchal's avatar
    gauravnarchal
    Post Prodigy
    4 years ago

    Hi v-chenwuz-msft  - Thank you for looking into this and helping me. I have attached my PBIX file for you to look at the data.

     

    Click Here

     

    Thanks

    • gauravnarchal's avatar
      gauravnarchal
      Post Prodigy
      4 years ago

      Hi v-chenwuz-msft  - I have tried your solution, but I am not able to get the desired results for Invoice numbers.

       

      I have attached my data again for you to see. Click Here

       

      Many thanks for your help in advance.

      • v-chenwuz-msft's avatar
        v-chenwuz-msft
        Community Support
        4 years ago

        Hi gauravnarchal .Your model has some problems if you want to do this ,you should edit them like the following make sure these tables can filter by Settlement table’s AUTH field.

         

         

        And this measure,but unselect the blank checkbox in the filter 

        Measure =
CONCATENATEX( 'ARInvoices', [invoice number], "/" )

         Pbix in the end.

         

        Best Regards

        Community Support Team _ chenwu zhu

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.