Hi,
I'm really seeking urgent help on building the revenue and client quintiles using adventure works 2019 dataset.
I'm using these tables only - Sales.Customer, Sales.SalesOrderHeader, Sales.SalesOrderDetail, Production.Product, Production.ProductSubcategory, Production.ProductCategory.
And measures only!
I've gone as far as:
1. Creating a measure using -
Customer Quintile =
VAR FirstQ =
PERCENTILEX.INC (
ALL (SalesOrderHeader ),
SalesOrderHeader[CustomerID],
.25
)
VAR SecondQ =
PERCENTILEX.INC (
ALL ( SalesOrderHeader ),
SalesOrderHeader[CustomerID],
.50
)
VAR ThirdQ =
PERCENTILEX.INC (
ALL ( SalesOrderHeader ),
SalesOrderHeader[CustomerID],
.75
)
VAR ThisVal =
MIN( SalesOrderHeader[CustomerID])
RETURN
IF (HASONEVALUE(SalesOrderHeader[CustomerID]),
IF(
ThisVal <= FirstQ,
1,
IF (
ThisVal > FirstQ
&& ThisVal <= SecondQ,
2,
IF ( ThisVal > SecondQ && ThisVal <= ThirdQ, 3, 4 )
)
))
2. Creating an independent table named Client Quintile and having column Quintile with values: 1, 2, 3, 4, 5
and another measure in the standalone Cleint Quintile table as:
Client Quintile values =
SWITCH(
SELECTEDVALUE('Client Quintile Table'[Client Quintile]),
0,
CALCULATE([Total Clients],
'Client Quintile Table'[Client Quintile]=1),
1,
CALCULATE([Total Clients],
'Client Quintile Table'[Client Quintile]=2),
2,
CALCULATE([Total Clients],
'Client Quintile Table'[Client Quintile]=3),
3,
CALCULATE([Total Clients],
'Client Quintile Table'[Client Quintile]=4),
4,
CALCULATE([Total Clients],
'Client Quintile Table'[Client Quintile]=5),
5
)
This is still not working when I'm trying to get these values in a chart visual without Customer ID.
I need help on how to create these quintiles properly and how to plot them in a line chart or show them in a table so that they can be used to slice my data correctly.
Thanks!
