Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi @sedison12
you can download my file here:
https://1drv.ms/u/s!AiiWkkwHZChHjyFEsCyL__k1n03c
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi @sedison12
you can download my file here:
https://1drv.ms/u/s!AiiWkkwHZChHjyFEsCyL__k1n03c
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi @LivioLanzo this was helpful but how can i show the 'sales amount' for the persons in each quartile instead of the 'Persons'?
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 ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
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
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 ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |