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 ), V...
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.

Whitewater100's avatar
Whitewater100
Icon for Solution Sage rankSolution 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
      Icon for Community Support rankCommunity 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.