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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Geilisa
Frequent Visitor

Create miscellaneous/other group using count below a threshold

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.  

Geilisa_0-1648560803002.png

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

Thank you.

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

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" ) )

 

 

Icey_0-1648795608532.png

 

2. Create a What-if parameter for threshold.

Icey_1-1648796698448.png

 

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.

Icey_2-1648796877052.png

Icey_0-1648798113711.png

 

 

Best Regards,

Icey

 

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

View solution in original post

6 REPLIES 6
Geilisa
Frequent Visitor

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

Whitewater100
Solution Sage
Solution Sage

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.

Whitewater100_1-1648562666626.png

 

 

Whitewater100_0-1648562609810.png

 

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. 

Icey
Community Support
Community Support

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" ) )

 

 

Icey_0-1648795608532.png

 

2. Create a What-if parameter for threshold.

Icey_1-1648796698448.png

 

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.

Icey_2-1648796877052.png

Icey_0-1648798113711.png

 

 

Best Regards,

Icey

 

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

Whitewater100
Solution Sage
Solution Sage

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!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors