Group Matrix into Dynamic Quartiles

[marksaba]

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!

[Anonymous · Accepted solution]

Hi @marksaba ,

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

[Ritaf1983]

Hi @marksaba 

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly