Need help on DAX

Sreerams26 — Thu, 03 Oct 2024 23:25:30 GMT

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

Re: Need help on DAX

Anonymous — Fri, 04 Oct 2024 05:34:20 GMT

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.

topic Need help on DAX in DAX Commands and Tips

Need help on DAX

Re: Need help on DAX