Quartile Groups

[Mainer04401]

I built the measure below for calculating quartiles.  It works when I pull it into a store level list, but if I just pull the measure into the view without any store level context, it just says 4th quartile because it's not iterating through the stores.  I must be missing something.

Quartile (Sales) =

VAR FirstQ =

calculate(PERCENTILEX.EXC(SUMMARIZE(Table,Table[Site #],"Sales",[Sales (Scan)]),[Sales],.25),allselected(Table[Site #]))

VAR SecondQ =

calculate(PERCENTILEX.EXC(SUMMARIZE(Table,Table[Site #],"Sales",[Sales (Scan)]),[Sales],.50),allselected(Table[Site #]))

VAR ThirdQ =

calculate(PERCENTILEX.EXC(SUMMARIZE(Table,Table[Site #],"Sales",[Sales (Scan)]),[Sales],.75),allselected(Table[Site #]))

VAR ThisVal =

    SELECTEDVALUE ( Table[Sales] )

    VAR StoreLevel = VALUES(Table[Site #])

RETURN

    IF ( [Sales (Scan)] <= FirstQ,

        "1st Quartile",

        IF (

            [Sales (Scan)] > FirstQ

                && [Sales (Scan)] <= SecondQ,

            "2nd Quartile",

            IF ( [Sales (Scan)] > SecondQ && [Sales (Scan)] <= ThirdQ, "3rd Quartile", "4th Quartile" )

        )

    )

[v-lili6-msft · Accepted solution]

hi @Mainer04401 

In your formula, the row context is store level context, so you need to add store level in the visual, and it also involves measure total problem.

Please refer to these:

https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/

https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/https://community.powerbi.com/t...

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Quartile (Sales) =

VAR FirstQ =

calculate(PERCENTILEX.EXC(SUMMARIZE(Table,Table[Site #],"Sales",[Sales (Scan)]),[Sales],.25),allselected(Table[Site #]))

VAR SecondQ =

calculate(PERCENTILEX.EXC(SUMMARIZE(Table,Table[Site #],"Sales",[Sales (Scan)]),[Sales],.50),allselected(Table[Site #]))

VAR ThirdQ =

calculate(PERCENTILEX.EXC(SUMMARIZE(Table,Table[Site #],"Sales",[Sales (Scan)]),[Sales],.75),allselected(Table[Site #]))

VAR ThisVal =

    SELECTEDVALUE ( Table[Sales] )

    VAR StoreLevel = VALUES(Table[Site #])

RETURN

    IF ( [Sales (Scan)] <= FirstQ,

        "1st Quartile",

        IF (

            [Sales (Scan)] > FirstQ

                && [Sales (Scan)] <= SecondQ,

            "2nd Quartile",

            IF ( [Sales (Scan)] > SecondQ && [Sales (Scan)] <= ThirdQ, "3rd Quartile", "4th Quartile" )

        )

    )

Regards,

Lin