Non selected items group as Others

[andrewnzinuk]

Hi All

I need some help with some DAX. We have a single table with Orders and the Vendor the order originated from. We have a donut chart that displays the order count by vendor. The vendor is a slicer on the report, so if you select one or more vendors you see the order count for those vendors. If all vendors are selected you see all orders for all vendors. So far so good.

I have a requirement to allow a user to select one or more vendors from the slicer and show those vendors on the donut chart. The second requirement is to show all the other orders grouped under a single group called others.

For example if I have Vendor A, Vendor B, Vendor C, Vendor D and Vendor E - all of which have orders. By default they all display in the donut chart. If the user selects Vendor A and Vendor B in the slicer, I would like their order counts to be displayed in the donut chart (which is the default behaviour), however I would like the order count for Vendor C, Vendor D and Vendor E to also be displayed as a single value called "others" for example which would be the total order count for Vendor C, Vendor D and Vendor E. We want to be able to show the market share of selected vendors against the whole market without being able to see the other individual vendors. If all Vendors are selected there would be no grouping and all vendors and their order counts would be shown on the donut chart.

I am struggling with the DAX - I think just need some pointers - but imagine it is reasonably simple and straighforward.

Any help much appreciated.

Regards

Andrew

[lbendlin]

Use a disconnected table that holds the Vendor names (for example create it as a calculated table via VALUES()).

Then use this table to feed the slicer.

Lastly, create a measure that looks a the FILTERS() of the slicer and then creates/assigns the group names accordingly for each of the items in your data visual.

You may run into issues where you can't use measures in certain parts of a visual.

[andrewnzinuk]

Hi

Not sure I understand. I get the creation of a calculated table with the distinct Vendor Values, but not not sure how you get the filter to work, some psudeo code of how the filter would work and group the data into "Others" would be helpful.

Thanks for you reply

Thanks

Andrew

[lbendlin]

Table VendorSales:  (Power Query)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkvNS8kvUjBU0lEyNTJQitWBCxkBhYwtUISMgUImqKpMgEJmBihCpiBVII2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Vendor = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales", Currency.Type}})
in
    #"Changed Type"

Table VendorSlicer:  (DAX)

VendorSlicer = VALUES(VendorSales[Vendor])

Measure VendorGroup:

VendorGroup := 
var s=filters(VendorSlicer[Vendor])
var v=SELECTEDVALUE(VendorSales[Vendor])
return IF(CONTAINSROW(s,v),v,"Other")

This gives a crude result as it needs the vendor field to be in the visual  but it gives you the general idea.

A proper solution would be more along these lines

https://blog.gbrueckl.at/2019/05/power-bi-dynamic-topn-others-with-drill-down/

[andrewnzinuk]

Hi 

That is starting to make sense. Quick question re the table VendorSlicer, do I create that in the Model. I am not sure where to create that. I created it as a unlinked table  in the model - and it appeared and I had the option convert to table ans I did that. I dragged the value onto the report and it looked like this :

VendorSlicer2 = VALUES(Orders[Vendor]), rather than the actual vendor values

Clearly I am doing something wrong  🙂

Thanks

Andrew