Calculate Total Invoice That Contains A Code

[villa1980]

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

[v-yifanw-msft]

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.

[bhanu_gautam]

@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]

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"??