Group not-selected suppliers into "Others"

[Adfc14]

Hi,

Can someone point me in the right direction please?

In a regular table, I need to show all vendors. 

But, as soon as one or more vendors are selected, the table should show the selected vendors plus an extra row indicating "Others".

The goal is to show total purchase value per vendor, but if selections are made on the vendor filter then it should show that or those vendors' sum + a sum for all other vendors.

Is this possible?

Thank you.

[Kushal_Chawda · Accepted solution]

@Adfc14  try below.

Data:
Load * Inline [
Vendors,Value
A,100
B,50
C,30
D,40
E,50 ];


Load * Inline [
Dim
1
2 ];

Create a table with below dimension and measure

// Dimension
=pick(Dim,
          Vendors,
                  if(GetSelectedCount(Vendors)>0,
                       'Others',
                        null()
                    )
      )

//Measure
pick(Dim,
        sum(Value),
                  if(GetSelectedCount(Vendors)>0,
                       sum(total{<Vendors>} Value)- sum(Value)
                    )
     )

Uncheck "Include null values" option for above dimension

Select Totals function as "Sum" for above measure

[Adfc14]

Hi Kushal, thank you for that.

Your sample data has 2 dimensions in it, "Dim" and "Vendors", while I only have one dimension, "Vendors". I tried to simply replace the "Dim" and the "Vendor" with my data's vendor fieldname and only add that field to the table but the table doesn't display any data.

Can you please assist further?

In my case, my Inline load would be something like:

Data:
Load * Inline [
Vendors,Value
A,100
B,50
C,30
D,40
E,50 ];


Load * Inline [
Vendors
1
2 ];

For additional context, I'm loading the vendors from the Vendor table, which links to the Fact table by a key field, and from that Fact table I'm pulling the "Value".

And thank you again for your time so far, it is much appreciated! 🙂

[Kushal_Chawda]

@Adfc14  In my example, Data table is main table which in your case is Vendor and Fact table. Apart from that you need to create inline table (island table) with Dim as column which will act as synthetic dimension in your chart. So you don't need to replace it with Vendor name. Keep that Dim column disconnected from actual table.

[Adfc14]

Hi Kushal,

Yep makes sense, thank you for explaining it to me!

And yes, now it works as planned, I was even able to make it show percentage of total if I need to now, so thank you again.

One final question: if I filter by the field "Supplier", it all works well, but if I filter using the supplier dimension in the table itself (i.e. the dimension you said to create) then it only shows that vendor and omits the "Others". 

I tried adding the "Supplier" (the data's field) to the Set Analysis within both the Dimension and the Measure formulas, but that didn't work (or I didn't do it correctly)...

If you've had enough helping me, and you have helped me a lot already trust me!, I fully understand. 🙂

Thank you.