Salesperson production $ in quartiles by region - dynamic

[sedison12]

I am trying to create a dynamic report to show each quartile (0-25%, 26-50%, 51-75%, 76-100%)based on the user selection for sales totals by salespeople in each particular region.

For example,

In region A: Salesperson 1 sold $150,000; Salesperson 2 sold $100,000, Salesperson 3 sold $75,000, and salesperson 4 sold $50,000

In region B: Salesperson 1 sold $300,000; Salesperson 2 sold $250,000, Salesperson 3 sold $285,000, and salesperson 4 sold $500,000

The user wants to see all regions, and quartile 1 (top 25% producers) for those regions:

So the report would show only: Region A Salesperson 1 and Region B Salesperson 4 - as those are the top quartile performers for each region

If they selected quartile 2, then Region A would show salesperson 2 and region B would show salesperson 3 - as those fall in the 26-50% quartile range

[LivioLanzo · Accepted solution]

Hi @sedison12

you can download my file here:

https://1drv.ms/u/s!AiiWkkwHZChHjyFEsCyL__k1n03c

[sedison12]

Hi @LivioLanzo this was helpful but how can i show the 'sales amount' for the persons in each quartile instead of the 'Persons'?

[LivioLanzo]

@sedison12

Did not test it but it should be something like:

Total Sales in Quartile =
IF (
    HASONEVALUE ( Regions[Region] ) && HASONEVALUE ( QuartilesRange[Category] ),
    VAR MinQrtIndex =
        MIN ( QuartilesRange[Index] )
    VAR MinQrt =
        MIN ( QuartilesRange[MinPerc] )
    VAR MaxQrt =
        MAX ( QuartilesRange[MaxPerc] )
    VAR SumTable =
        SUMMARIZE ( Sales, Regions[Region], Persons[Persons] )
    VAR HigherQrtile =
        PERCENTILEX.INC ( SumTable, CALCULATE ( SUM ( Sales[Amount] ) ), MaxQrt )
    VAR LowerQrtile =
        PERCENTILEX.INC ( SumTable, CALCULATE ( SUM ( Sales[Amount] ) ), MinQrt )
            - IF ( MinQrtIndex = 4, 1, 0 )
    VAR PeopleList =
        FILTER (
            SumTable,
            VAR SalesAmount =
                CALCULATE ( SUM ( Sales[Amount] ) )
            RETURN
                AND ( SalesAmount > LowerQrtile, SalesAmount <= HigherQrtile )
        )
    RETURN
        CALCULATE ( SUM ( Sales[Amount] ), PeopleList )
)

[sedison12]

Hi @LivioLanzo,

While your sample worked in the simplistic example I gave. It still does not appear to work for a more realistic example.

Each of these sales people have multiple sales on different dates throughout the year.

How can we aggregate or sum all of the individual sales rolled up into region and date hierarchies (monthly, quarterly, and yearly) and then place them into their respective quartiles?

ex: ROLLUP for January sales -

Persons	 Amount 	Region	Date
Person 1	 $150,000 	A	1/25/2018
Person 2	 $100,000 	A	1/21/2018
Person 3	 $75,000 	A	1/16/2018
Person 4	 $50,000 	A	1/23/2018
Person 1	 $300,000 	B	1/15/2018
Person 2	 $250,000 	B	1/11/2018
Person 3	 $285,000 	B	1/18/2018
Person 4	 $500,000 	B	1/4/2018
Person 1	 $75,000 	A	1/24/2018
Person 2	 $10,000 	A	1/5/2018
Person 3	 $54,000 	A	1/8/2018
Person 4	 $35,000 	A	1/25/2018
Person 1	 $89,500 	B	1/14/2018
Person 2	 $400,000 	B	1/30/2018
Person 3	 $34,050 	B	1/2/2018
Person 4	 $200,350 	B	1/3/2018

[LivioLanzo]

@sedison12

Are you looking for something like this?

File: https://1drv.ms/u/s!AiiWkkwHZChHjy7GSSA62piBCYft

Additive by Regions =
IF (
    HASONEVALUE ( Quartiles[Index] ),
    VAR MinQrt =
        MIN ( Quartiles[Min] ) -- selected quartile perc lower bound
    VAR MaxQrt =
        MAX ( Quartiles[Max] ) -- selected quartile perc upper bound
    VAR LowerQuartile =
        -- value of lower quartile
        PERCENTILEX.INC (
            VALUES ( Persons[Persons] ),
            CALCULATE ( SUM ( Sales[Amount] ), ALLSELECTED (), CALCULATETABLE ( Persons ) ),
            MinQrt
        )
    VAR UpperQuartile =
        -- value of upper quartile
        PERCENTILEX.INC (
            VALUES ( Persons[Persons] ),
            CALCULATE ( SUM ( Sales[Amount] ), ALLSELECTED (), CALCULATETABLE ( Persons ) ),
            MaxQrt
        )
            + IF ( MaxQrt = 1, 1, 0 )
    VAR PersonsInQuartile =
        FILTER (
            VALUES ( Persons[Persons] ),
            VAR SaleAmount =
                CALCULATE ( SUM ( Sales[Amount] ), ALLSELECTED (), CALCULATETABLE ( Persons ) )
            RETURN
                AND ( SaleAmount >= LowerQuartile, SaleAmount < UpperQuartile )
        )
    RETURN
        CALCULATE ( SUM ( Sales[Amount] ), PersonsInQuartile )
)