"Markin" a category - using single row vlaue to sum multiple rows , or row category

[ben harel]

A real life problem guys,

i have a data set that goes like this:

colums - [invoice num] [department num] [date] [item num] [Net charge]
each invoice contains multiple items and so invoice number is repeated for many rows

invoice	deparment	item num	net charge
1000	1	fdd	30
1000	6	g	1
1000	2	ggd	100
1001	4	g	15
1001	1	fdd	30
1002	2	fdd	20
1002	5	g	50
1003	4	h	6
1003	8	5tt	50

<tbody>
</tbody>

what i want is to aggregate the Net charge for all invoice containing a even a single item from department num 4
for our example, if invoice 1001 and 1003 have many items , and also items from department 4 , i want the sum, of 1001 and 1003 - so its15+30+6+50

so in fact i want to mark a category by a value in a single row.

at the moument i do this by pivoting invoice and filtering dep 4. i get a list of all invoices containing even 1 row of department 4. i create a new table in power pivot and create a relationship and than i have a category of invoices that are "marked"
i can than sum these marked invoices , this could be done automatic if i could pivot a pivot table, but i cant

is there a straightforward way of doing this kind of "marking" ?

 

[Meggesto]

So I may have a solution that could work for you. I added two additional columns to your data to be used with a pivot table. First I added a COUNT column designed to return the value of 1 if there was only 1 ITEM NUMBER listed. This may take some playing with depending on how your data is organized but I'm sure there's a way to do it. I then added a TOTALS Column using the SUMIF Function. It provided a total amount for each invoice. Using a pivot table I Filtered by Department and Count with my Row Labels as the Invoice Number and Totals column to be Summed. If you can get derive a formula that returns a 1 value for your Items with only 1 number and SORT your invoice numbers and use SUMIF; this should work for you.

 

[anvg]

Hi

if i could pivot a pivot table, but i cant

You can!

CALCULATE(SUM('Table1'[net charge]), SUMMARIZE(FILTER('Table1', 'Table1'[deparment] = 4), 'Table1'[invoice]))

Regards,

 

[ben harel]

thank you Meggesto and anvg,

anvg - the result of the code you worte is the sum of invoices that have a row with department 4, the only problem is that FILTER function excludes all rows of items that are not department 4 , therefor giving me an incomlete invoice.
in our example the result of this code for invoice 1003 is 6, where i am looking for 50+6 (FILTER exclude the last ROW from the calculation)

BUT you gave me an idea !

i used measure 1: CALCULATE(SUM('table1'[net charge],ALL('table1'[department]))
this give me the sum regardless
than i used this in measure2:=CALCULATE([Measure1],FILTER('table1','table1'[deparment]="4"))
and put the filter above the filter result

BRAVO !!

and thanks alot for the inspiration !!

 

[anvg]

Hi, ★ ben harel
Sorry, but I knew nothing about a structure of your pivot table. I used a simple pivot where invoice was a row source and my measure was calcuated cells ; )

Regards,

 

[ben harel]

correct, tried it on the simplfied version and yours works perfectly as well !

thanks for a great tip !

regards,
ben