Group entries together that aren't selected in slicer?

[Anonymous]

Hi, I have a table that looks like the below. I have a slicer that contains the "Supplier" field so the users can view this data for any supplier they select.

I want to create a line chart that shows me the total sales per date of the selected supplier, with all other suppliers that aren't selected showing as "Other Suppliers" with an AVG of their sales across the same date range. Something like the below..

So if supplier A was selcted in the slicer, it would show the sales for supplier A then the AVG of all other sales under "Other Suppliers". This would need to be dynamic so if supplier B is selected it will do the same as above.

Is this something that can be acheived and does anyone know the correct way of implementing this? 🙂

Thanks

[v-yalanwu-msft]

Hi, @Anonymous ;

Is your problem solved? If so, Would you mind accept the helpful replies as solutions? Then we could close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

@jd8766  ;
As By not selecting the supplier in the slicer then this measure won't pick up the unselected suppliers? 
so I create another slicer table without relationship , which could use measure to calculate the result what i selected.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @Anonymous ;

It is not feasible to display A or B dynamically, because Legend in the line chart can only be column, not measure, so it can only be A static name at present.
So there are alternatives.
1.create a table as slicer

slicer = SUMMARIZE('Table',[Supplier])

2.enter a table as Legend.

3.create a measure.

averge = IF(MAX('Table2'[Supplier])="Main",
CALCULATE(AVERAGE('Table'[SaleCount]),FILTER( ALLSELECTED('Table'),[SoldDate]=MAX('Table'[SoldDate])&&[Supplier] in ALLSELECTED(slicer[Supplier]))),
CALCULATE(AVERAGE('Table'[SaleCount]),FILTER( ALLSELECTED('Table'),[SoldDate]=MAX('Table'[SoldDate])&&NOT( [Supplier]) in ALLSELECTED(slicer[Supplier]))))

4.could add a tooltip

Tooltip = IF(MAX('Table2'[Supplier])="Main",MAX('slicer'[Supplier]),MAX('Table2'[Supplier]))

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Try like for other line

AverageX(Values(Table[Supplier]), calculate([Measure], filter(Table, Table[Supplier] <> selectedvalue(Table[Supplier]))))

or

AverageX(Values(Table[Supplier]), calculate([Measure], filter(Table, not Table[Supplier] in allselected(Table[Supplier]))))

[jd8766]

I have a similar issue and just tried this method but it won't work. By not selecting the supplier in the slicer then this measure won't pick up the unselected suppliers?