Sum of Item Groups with partial filter

[NiugeS]

Hi,

Still learning PowerBi so apologies in advance if this is easy or if I do not explain myself properly.  

I have a table as per below.  In my report, I have a slicer for sales person and table for Item and Price which works fine.  So if I pick person B, it shows me total sales = 3.   In a seperate chart, I am looking for a sum of all sales by category but filtered on the sales person.  So if Sales person A is selected, Apples = 6, Meat = 10, if Sales Person B is selected, it is filtered to only show Apple = 6.  At the moment, the 2nd chart only gives me the category total of what the selected sales person has sold rather than the category sales totals.  Hope that makes sense.  

Sales Person	Item	Price	Category
A	Apple	1	Fruit
A	Steak	5	Meat
B	Apple	1	Fruit
B	Apple	1	Fruit
B	Apple	1	Fruit
C	Steak	5	Meat
C	Apple	1	Fruit
C	Apple	1	Fruit

Thank you

[littlemojopuppy]

Is this along the lines of what you're expecting?

Measure definitions:

• Sales:=SUM(Table1[Price])
• Sales Person Sales:=CALCULATE(SUM(Table1[Price]),ALLSELECTED(Table1[Sales Person]))
• Category Sales:=CALCULATE(SUM(Table1[Price]),ALLSELECTED(Table1[Category]))

[NiugeS]

@littlemojopuppy  Thank you - not sure what i'm doing wrong but doesn't give the desired result.

Tried to add a screen shot 3 times but failing.  If i select sales person C, i still get 5 steak, 2 apple and 7 total..

[littlemojopuppy]

Can you share a screen snip?

[NiugeS]

[littlemojopuppy]

Apologies...I misunderstood what you were asking.  Is this better?

Total Sales = SUM('Table'[Price])

Sales Person Sales = 
    CALCULATE(
		[Total Sales],
		FILTER(
            ALL('Table'),
            'Table'[Sales Person] = SELECTEDVALUE('Table'[Sales Person])
        )
    )

If you want category or item sales, replace Sales Person with Category or Item in the SELECTEDVALUE statement.  But make sure there's a slicer for those.

[NiugeS]

@littlemojopuppy Sorry - don't think i've explained myself or not sure I am completely following.  Hopefully the below helps..  I've given the results of what i am looking for Chart 1 and Chart 2

	Sales Person	Item	Price	Category
	A	Apple	1	Fruit		Salse Person A Selected
	A	Steak	5	Meat
	B	Apple	1	Fruit	Example 1	Chart 1 (Sales per person)		Chart 2 (total sales of items)
	B	Apple	1	Fruit		Item	Total Sales	Item	Total Sales
	B	Apple	1	Fruit		Apple	1		Apple	6
	C	Steak	5	Meat		Steak	5		Steak	10
	C	Apple	1	Fruit
	C	Apple	1	Fruit
						Salse Person B Selected
					Example 2	Chart 1 (Sales per person)		Chart 2 (total sales of items)
						Item	Total Sales	Item	Total Sales
						Apple	3		Apple	6
						Steak	0		Steak	10

[littlemojopuppy]

Before I write code...just to be certain what you are looking for is to ignore the filter on sales person when calculating the item totals, correct?

[NiugeS]

@littlemojopuppy  My example above isn't correct as Chart 2 shouldn't for Sales Person B shouldn't include Steaks.

What I was hoping for is to have the filter still show the items that the person sold (i.e. Sales Person B only sold apples so the 2nd chart would only show Apples) but give a total sales for all Apples sold accross A, B and C.

So the 2nd Chart with Sales Person B filtered would look like this:

Item           Total Sales

Apples       6

Sorry for all confusion and appreciate the help.

[littlemojopuppy]

Try this.  The formula is correct but you'll have to change the axes.

[NiugeS]

Thank you.   Is it possible for me to create a new chart and only have the items sold by that Sales Person but have the total item sales for that item (For example screen shot below but instead of sum of Sales Person B's total of $3 it give the sum of all sales people for that item $6)?

Thanks

[littlemojopuppy]

If I answered your initial question, I'd appreciate it if you marked it as a solution.

Like this?

Modify the formula for Total Item Sales to use Sales Person instead

[NiugeS]

Almost there - if you select Sales Person B and it only shows Apples $6, than yes, that is exactly what I was looking for.  Sorry my terminology has been so poor.

[littlemojopuppy · Accepted solution]

First, I do believe based on what you provided I answered your original question.  And the solution for your second question isn't far away from the first.

Second, and more importantly, I've been participating in forums like this for TSQL, Power BI, and Visual Basic for a long time.  And it's generally considered very poor form to ask someone to "do it for me".  If someone is going to take time out of their day to help you, they want to see you try as well.

Good luck!

[NiugeS]

@littlemojopuppy  Thank you and that is the best response ever!  That's a fairly big assumption I haven't been google searching and attempting this for quite some time before even reaching out on PowerBi.  Not to worry, I'll continue trying.

As for my original question, it read as follows.

So if Sales person A is selected, Apples = 6, Meat = 10, if Sales Person B is selected, it is filtered to only show Apple = 6. 

That's pretty much what I was asking for.

All the best and stay safe!