topic Re: Create miscellaneous/other group using count below a threshold in DAX Commands and Tips

Create miscellaneous/other group using count below a threshold

Geilisa — Tue, 29 Mar 2022 13:41:34 GMT

I've got a Line and Stacked Column Chart (using this because of Pareto line). I used to create a measure "Count".

Count =
CALCULATE (
    COUNTROWS ( Projects ),
    ALLSELECTED ( Projects ),
    VALUES ( Projects[Points] )
)

"Project[Points]" is the Shared axis and "Count" is the Column values.

Now I want to use this measure and combine any calculated values below 3 into a miscellaneous/other group. So it would be removing "M" thru "g", and creating an "other", stacking them all together.

I've been using power bi for about 2 months now. So the more steps explained the better.

Thank you.

Re: Create miscellaneous/other group using count below a threshold

Whitewater100 — Tue, 29 Mar 2022 13:55:55 GMT

Hi:

Without seeing your data and tables I can guess if you want to group your measure for points under three, you could try"

Group ❤️ = CALCULATE([Count], FILTER(Projects, Projects[points] <= 3)) **where [Count is your existing measure)
You might need to subtract this value from your Count measure if you are trying to separate ❤️ and >3.

I hope this helps!

Re: Create miscellaneous/other group using count below a threshold

Whitewater100 — Tue, 29 Mar 2022 13:56:37 GMT

I don't know how hearts appeared, I try to bold measure...

Re: Create miscellaneous/other group using count below a threshold

Whitewater100 — Tue, 29 Mar 2022 14:05:00 GMT

Hi:

The other way is to Rclick the field on the right (columns with the values you want to consolidate) and choose New Group.

Here's an image you can bucket values or other fields this way.

Re: Create miscellaneous/other group using count below a threshold

Geilisa — Tue, 29 Mar 2022 15:10:05 GMT

Thank you for your reply. I ended up doing a string of if statments to just rename the project names to "other" manually, unfortunetly this wont auto update based on if its lessthan or equal to 3. But it works for now...

Re: Create miscellaneous/other group using count below a threshold

Geilisa — Tue, 29 Mar 2022 15:20:17 GMT

I see this does work, but it isnt exactly what I'm looking for. Hoping to make it automatic. But yes it does work for grouping. Took me a little experimenting.

Re: Create miscellaneous/other group using count below a threshold

Icey — Fri, 01 Apr 2022 07:28:44 GMT

Hi Geilisa ,

Try this:

1. Create a table used for shared axis and DO NOT create relationships between this table and your fact table.

PointGroup = UNION ( DISTINCT ( Projects[Points] ), ROW ( "Others", "Others" ) )

2. Create a What-if parameter for threshold.

3. Create measures.

ModifiedCount = VAR CurPoint_ = MAX ( PointGroup[Points] ) RETURN CALCULATE ( COUNTROWS ( Projects ), Projects[Points] = CurPoint_ )

ModifiedCount 2 = VAR threshold_ = [threshold Value] VAR CurPoint_ = MAX ( PointGroup[Points] ) RETURN IF ( [ModifiedCount] > threshold_, [ModifiedCount], IF ( CurPoint_ = "Others", SUMX ( FILTER ( ALLSELECTED ( PointGroup ), [ModifiedCount] <= threshold_ ), [ModifiedCount] ) ) )

4. Create a visual.

Best Regards,

Icey

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