topic Re: Aggregate on Invoice Header level, then filter on the aggregated value. in DAX Commands and Tips

Aggregate on Invoice Header level, then filter on the aggregated value.

Anonymous — Fri, 17 Feb 2023 09:13:08 GMT

Hi,

I'm fairly new to DAX, and have been fighting with this problem for days now, also trying to find a solution on the internet.

I think that this problem is probably quite easy and I'm just thinking too complicated.

Basically I have a "Sales" table, that has invoices on invoice item level, like so:

Invoice	Artikle	Value
A	XX	20
A	YY	10
A	Shipping	5
B	ZZ	5
B	XX	20
B	Shipping	5
C	ZZ	5
C	YY	10
C	Shipping	5

I want to aggregate the invoice total, but without shipping cost (product cost only), and then show the product cost of all invoices above/below a certain values. i.e. Show the total (without shipping) of all invoices with a product value of > 15. Which here would be 55 (=30 from A and 25 from B).

I've started by creating a measure for the total without shipping, but then fail to use it as a filter:

Total_product_cost_belowX =
var ProductCost = CALCULATE(SUMX(VALUES(Sales[Invoice]),Sales[Value]),Sales[Artikle] <>"Shipping") // works

from here...?

Thanks a lot in advance!

Re: Aggregate on Invoice Header level, then filter on the aggregated value.

johnt75 — Fri, 17 Feb 2023 09:40:13 GMT

One approach would be to create a summary table in the model, e.g.

Invoice Summary = ADDCOLUMNS ( DISTINCT ( 'Table'[Invoice] ), "Sales amount", [Total_product_cost_belowX] )

You could then create measures and filters against this new table.

Re: Aggregate on Invoice Header level, then filter on the aggregated value.

tamerj1 — Fri, 17 Feb 2023 09:41:15 GMT

Hi Anonymous
When you say "of all invoices with a product value of > 15." what do you exactly mean? Please clarify.

Re: Aggregate on Invoice Header level, then filter on the aggregated value.

Anonymous — Fri, 17 Feb 2023 11:05:31 GMT

Hi tamerj1, each invoice has in the article column real products (XX, YY, ZZ..) but also an item "shipping".

With product value I mean the invoice total minus the shipping cost.
So for invoice A the invoice total would be 35, but the product value is only 30.

Important: I can't just hard code to substract 5, as shipping costs are different in my real world application.

Hope that clarifies it.
Best, nuclePBI

Re: Aggregate on Invoice Header level, then filter on the aggregated value.

tamerj1 — Fri, 17 Feb 2023 11:23:49 GMT

Anonymous
Please find attached sample file with the solution

Invoice Total > 15 = SUMX ( VALUES ( 'Sales'[Invoice] ), VAR CurrentInvoiceValue = CALCULATE ( SUM ( 'Sales'[Value] ), 'Sales'[Artikle] <> "Shipping" ) RETURN IF ( CurrentInvoiceValue > 15, CurrentInvoiceValue ) )

Re: Aggregate on Invoice Header level, then filter on the aggregated value.

Anonymous — Fri, 17 Feb 2023 14:18:38 GMT

Works perfectly, thanks a ton tamerj1