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.

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.