Forum Discussion

Sreerams26's avatar
Sreerams26
Frequent Visitor
1 year ago
Solved

Need help on DAX

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
"DAX " "DAX help" 'Need help' 'DAX'
"powerbi"
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.