Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Group entries together that aren't selected in slicer?

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.

JD09631_0-1647264050564.png

 

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..

JD09631_1-1647264186489.png


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

 





4 REPLIES 4
v-yalanwu-msft
Community Support
Community Support

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
Community Support
Community Support

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.

vyalanwumsft_0-1647496666498.png

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:

vyalanwumsft_1-1647496954988.png


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
Super User
Super User

@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]))))

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?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.