Forum Discussion

villa1980's avatar
villa1980
Resolver II
2 years ago

Calculate Total Invoice That Contains A Code

Hi all,

 

 I have been asked to show the total invoice value based on that invoice containing a particluar code.

 I can bring back the invoice that contains the code however I cannot bring the total value.

Hope this makes sense

 

 This brings back the value of the code.

 

CALCULATE (
 sum( 'PBI VW_SDW_FAC_SALE_TRANS'[LINE_SELLING_VALUE]),
 FILTER('PBI VW_PRODUCTS_NEW',
 'PBI VW_PRODUCTS_NEW'[CLEARCLASS_LEVEL_3_CODE] = "LS" ))
 
Many Thannks
Need Help

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    2 years ago

    villa1980 , Try using below mentioned method

     

    TotalInvoiceValueWithCode =
    CALCULATE (
    SUM ( 'PBI VW_SDW_FAC_SALE_TRANS'[LINE_SELLING_VALUE] ),
    FILTER (
    'PBI VW_SDW_FAC_SALE_TRANS',
    RELATED ( 'PBI VW_PRODUCTS_NEW'[CLEARCLASS_LEVEL_3_CODE] ) = "LS"
    )
    )

     

    Please accept as solution and give kudos if it helps

    • villa1980's avatar
      villa1980
      Resolver II
      2 years ago

      Thank-you for the reply, however the related part of this does not bring back the table, I think the table within the related part has a many to many relationship.
      Could I do anything with a slicer that brings back those invoice numbers that contain "LS"??

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi villa1980 ,

    Thanks bhanu_gautam  for the prompt reply. I have created an example for you to solve the problem. You can follow the steps below:
    1. Create a new CodeTable and establish a relationship with PBI VW_SDW_FAC_SALE_TRANS.

     

    CodeTable = DISTINCT('PBI VW_SDW_FAC_SALE_TRANS'[CODE])

     

    2.Create a new measure.

     

    TotalInvoiceValueWithCode = 
CALCULATE (
    SUM ( 'PBI VW_SDW_FAC_SALE_TRANS'[LINE_SELLING_VALUE] ),
    FILTER ( 'CodeTable', 'CodeTable'[CODE] = SELECTEDVALUE ( CodeTable[CODE] ) )
)

     

    Final output:

    How to Get Your Question Answered Quickly - Microsoft Fabric Community

    If it does not help, please provide more details with your desired out put and pbix file without privacy information.

     

    Best Regards,

    Ada Wang

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