Need help on DAX

[Sreerams26]

I have two tables - Sales Invoice Lines containing sales transaction and Ledger table containing all financial accounting entries.  Both these tables have voucher as common item and hence a relation is created.  i need to populate customer's itemized statement in PBI.  I have all the details required in sales invoice except GST and PST.  In the sales, tax is total whereas tax is split in two lines under GL 271000 and 274000 in the ledger table.  when I create a basic measure and/or column for GST and PST, the visual is bringing the GST for all the transaction.  but I want ot display the GST and PSt only for the tax impacted line item.  my table is as below. 

in the above, total tax is applicable only on 1 line item.  how do I populate the GST and PST only to those lines.

my measures are 

GST = CALCULATE(SUM('TAX Entries'[TRANSACTIONCURRENCYAMOUNT]), FILTER('TAX Entries', 'TAX Entries'[MAINACCOUNTID]="271000")) and 

PST = CALCULATE(SUM('TAX Entries'[TRANSACTIONCURRENCYAMOUNT]), FILTER('TAX Entries', 'TAX Entries'[MAINACCOUNTID]="274000"))

I have tried columns as 

GSTAX = IF('TAX Entries'[MAINACCOUNTID]="271000", 'TAX Entries'[TRANSACTIONCURRENCYAMOUNT],0)

PSTAX = IF('TAX Entries'[MAINACCOUNTID]="274000", 'TAX Entries'[TRANSACTIONCURRENCYAMOUNT],0).

please advise

[Anonymous · Accepted solution]

Hi @Sreerams26 ,

Please try the following measures:

GST_Conditional = 
IF(
    SELECTEDVALUE('Sales Invoice Lines'[TotalTax]) > 0, 
    CALCULATE(SUM('TAX Entries'[TRANSACTIONCURRENCYAMOUNT]), 
        FILTER('TAX Entries', 'TAX Entries'[MAINACCOUNTID] = "271000" && SELECTEDVALUE('Sales Invoice Lines'[Voucher]) = 'TAX Entries'[Voucher])
    ),
    ""
)

PST_Conditional = 
IF(
    SELECTEDVALUE('Sales Invoice Lines'[TotalTax]) > 0, 
    CALCULATE(SUM('TAX Entries'[TRANSACTIONCURRENCYAMOUNT]), 
        FILTER('TAX Entries', 'TAX Entries'[MAINACCOUNTID] = "274000" && SELECTEDVALUE('Sales Invoice Lines'[Voucher])= 'TAX Entries'[Voucher])
    ),
    ""
)

 Result for your reference:

If this is not feasible, please feel free to reply!

Best regards,

Joyce

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