Forum Discussion
Sum only Unique Value for Invoice Number
LeahGJ ,
you might need something like this:
measure_Item = VAR currentInvNum = MAX ( T[Invoice Number] ) VAR ItemLineType = "ITEM" RETURN CALCULATE ( MAX ( T[Line Distribution Amount] ), FILTER ( ALL ( T ), T[Invoice Number] = currentInvNum && T[Line Type] = ItemLineType ) )If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
Hi,
the expected result is to show only the invoice amount and correct grand totals for both "ITEM" and "TAX".
The full data set has multiple invoices against the PO number. The grand totoal for the invoices against the PO is $11Mn. however, with the multiple payments to the indivisal invoice "10", the value is totalling as $49Mn.
the total invoice amount is $6,525,000.00, of which tax is 725,00.00. therefore i will for Power BI to show the results as follows:
Invoice # ITEM TAX Grand Total 10 5,800,000.00 725,000.00 6,525,000.00 12 112,500.00 11,250.00 123,750.00 Total 5,912,000.00 736,250.00 6,648,250.00 - ERD
Community Champion
LeahGJ ,
you might need something like this:
measure_Item = VAR currentInvNum = MAX ( T[Invoice Number] ) VAR ItemLineType = "ITEM" RETURN CALCULATE ( MAX ( T[Line Distribution Amount] ), FILTER ( ALL ( T ), T[Invoice Number] = currentInvNum && T[Line Type] = ItemLineType ) )If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
The formula worked partially. the grand total is off.