Forum Discussion

Geilisa's avatar
Geilisa
Frequent Visitor
4 years ago
Solved

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.  

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

Thank you.

CALCULATE
COLUMN VALUES
COUNT
COUNTROWS
DAX
SHARED AXIS
stacked column chart
values
  • Icey's avatar
    Icey
    4 years ago

    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.

6 Replies

  • Whitewater100's avatar
    Whitewater100
    Solution Sage
    4 years ago

    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!

    • Whitewater100's avatar
      Whitewater100
      Solution Sage
      4 years ago

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

  • Whitewater100's avatar
    Whitewater100
    Solution Sage
    4 years ago

    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.

     

     

     

    • Geilisa's avatar
      Geilisa
      Frequent Visitor
      4 years ago

      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's avatar
        Icey
        Community Support
        4 years ago

        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.

  • Geilisa's avatar
    Geilisa
    Frequent Visitor
    4 years ago

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