Grouping categories by selecting from table visual

[Anonymous]

I have a table visual that looks something like this:

category	value
A	3123
B	542
C	753
D	124

Basically a category and a corresponding value. I want to be able to, for example, select category A then somehow group all the other categories into an 'Other' category where it's the sum of their values. Or just select all categories I want under 'Other' and group them.

From the table vidual, I can't seem to group anything; the option is disabled. Not sure why, but how would I best do this? Preferrably without using measures/calculated columns if possible. 

[v-stephen-msft]

Hi @Anonymous ,

Here's my solution.

1.Create a new table by entering data.

2.Create a measure to calculate the value.

filtered value =
IF (
    MAX ( 'Table (2)'[category] ) = "other",
    SUMX ( ALL ( 'Table' ), [value] )
        - CALCULATE (
            SUM ( 'Table'[value] ),
            FILTER ( 'Table', [category] IN ALLSELECTED ( 'Table'[category] ) )
        ),
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER ( 'Table', [category] = MAX ( 'Table (2)'[category] ) )
    )
)

3.Results.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PaulDBrown · Accepted solution]

Here is one way:

1) Create a table using:

Join via inactive relationship

The create the measures:

Sum Value =
CALCULATE (
    SUM ( 'Table'[value] ),
    USERELATIONSHIP ( 'Other Table'[Other category], 'Table'[category] )
)

Other Value =
VAR _All =
    CALCULATE ( SUM ( 'Table'[value] ), ALL ( 'Table'[category] ) )
RETURN
    _All - CALCULATE ( [Sum Value], ALL ( 'Other Table'[Other category] ) )

Totals Measure =
IF (
    SELECTEDVALUE ( 'Other Table'[Other category] ) = "Other",
    [Other Value],
    [Sum Value]
)

and the final measure for the visual:

Final Measure = 
IF(
   ISFILTERED('Table'[category]), 
    SUMX(ADDCOLUMNS(VALUES('Other Table'[Other category]), "_totals",[Totals Measure]), [_totals]), [Sum Value])

To get this:

I've attached the sample PBIX file

[Aburar_123]

Hi,

Please find the below solution,

I have the data as below,

I want to group the city code. DEL is one group and MI, SRH are coming under the group called Other. Below are the steps,

Click the three dots corresponding tho the "City code" column then select "New Group" option in the popup list,

then, Click on the City code "DEL" and Click Group button at the bottom,

then click on "Include Other group" check box to create the Other category automatically as like below,

Final result is as below,

Please like the solution if it solves your scenario and let me know if you expect anything else.

[Anonymous]

Thanks for the answer. I tried this but unfortunately, end-users can't really do that. I want them to be able to just select the categories they want to display and have all other categories as others.

I guess the only way to have it dynamics is to use a measure. It worked for me but I had to use a slicer to select, not click on the table visual categories. 

[aj1973]

Hi @Anonymous 

I think this should do the work

[Aburar_123]

Hi,

I have just taken reference from @PaulDBrown and created the below single measure,

Final Measure =

IF(MAX('Other Table'[Other category])="Other", CALCULATE(SUM('Table'[value]),FILTER(all('Table'),NOT('Table'[category]) in VALUES('Table'[category]))),CALCULATE(SUM('Table'[value]),FILTER('Table','Table'[category] =max('Other Table'[Other category]))))