Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Group Matrix into Dynamic Quartiles

I need assistance learning how to create dynamic quartile groupings that, when inputted into a matrix, will group by rows.

 

For instance, I have a table with Items in rows and Dollar Sales and Unit Sales as values. I want this quartile to be inputted before Items, showing Top 25% of Products by Dollar Sales, Next 25%, Next 25%, and Bottom 25%, each of which I can expand to view Items. If I were to have 200 items, this grouping would group the items into four groups of 50. If I filtered the table via a slicer to cut down the item list to 100 items, I would then want the groupings to group items into four groups of 25. 

 

Can somebody assist with how this can be achieved?

 

Thank you!

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

     

    Ritaf1983 , thanks for your concern about this case. I tried to create a sample data myself based on the user's requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution: 

     

    I am using the financials table provided by power bi as sample data.

     


    1. create MEASURE to get the ranking of current sales.

    MEASURE = 
VAR _rank =
    RANKX ( ALLSELECTED ( financials ), [_sum_sales],, ASC, DENSE )
RETURN
    _rank

     

    2. Create MEASURE to display different names based on the rank.

    Measure2 = 
VAR _max_rank =
    MAXX (
        SELECTCOLUMNS (
            ALLSELECTED ( financials ),
            'financials'[Product],
            "_Measure", [MEASURE]
        ),
        [_Measure]
    )
VAR _g1 =
    DIVIDE ( _max_rank, 4 )
VAR _g2 =
    DIVIDE ( _max_rank, 2 )
VAR _g3 = _g1 * 3
RETURN
    SWITCH (
        TRUE (),
        [MEASURE] <= _g1, "G1",
        [MEASURE] > _g1
            && [MEASURE] <= _g2, "G2",
        [MEASURE] > _g2
            && [MEASURE] <= _g3, "G3",
        [MEASURE] > _g3
            && [MEASURE] <= _max_rank, "G4"
    )

     

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

     

    Ritaf1983 , thanks for your concern about this case. I tried to create a sample data myself based on the user's requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution: 

     

    I am using the financials table provided by power bi as sample data.

     


    1. create MEASURE to get the ranking of current sales.

    MEASURE = 
VAR _rank =
    RANKX ( ALLSELECTED ( financials ), [_sum_sales],, ASC, DENSE )
RETURN
    _rank

     

    2. Create MEASURE to display different names based on the rank.

    Measure2 = 
VAR _max_rank =
    MAXX (
        SELECTCOLUMNS (
            ALLSELECTED ( financials ),
            'financials'[Product],
            "_Measure", [MEASURE]
        ),
        [_Measure]
    )
VAR _g1 =
    DIVIDE ( _max_rank, 4 )
VAR _g2 =
    DIVIDE ( _max_rank, 2 )
VAR _g3 = _g1 * 3
RETURN
    SWITCH (
        TRUE (),
        [MEASURE] <= _g1, "G1",
        [MEASURE] > _g1
            && [MEASURE] <= _g2, "G2",
        [MEASURE] > _g2
            && [MEASURE] <= _g3, "G3",
        [MEASURE] > _g3
            && [MEASURE] <= _max_rank, "G4"
    )

     

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

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