Hi, thanks for your message. Let me elaborate on the problem. I am attaching the dummy data and a .twbx file I tried to work on (needs some help to match the sample dashboard) for reference. Please see below:
XYZ is a retail company and sells different types of SKUs in various categories (Biscuits, bakery,
etc).
Dataset:
The transaction data is reported at Invoice Number and SKU Level (i.e. every row is unique
at Invoice Number and SKU Level)
We need to create a dashboard as per below requirements.
A scatter plot with following Y axis and X Axis
o Y axis: Y axis is the “Average Profit Margin” which is the ratio of sum of profit to
the sum of sales for any given category
o X Axis: X axis is the “Average Bill Penetration” which is defined as the ratio of
unique invoices in a category to the total number of unique invoices
o Lines: Vertical line and horizontal line divides the scatter plot into 4 quadrants. The
vertical line and horizontal line should dynamically change with “Threshold Bill
Penetration” and “Threshold Gross Margin” values (input by user)
o Points: Point represents individual category. Each point is colored according to the
quadrant it falls into
o Sample dashboard attached – refer “Sample Dashboard.jpg”
Tooltip
o Category
o Avg. Bill Penetration
o Average Gross Margin
o Quadrant
o Threshold Bill Penetration
o Threshold Gross Margin
Filters: Add “Business Segment” filter
Assumptions (based on above statements):
1) Average Profit Margin = sum([Profit]) / sum([Sales])
2) Average Bill Penetration = COUNTD([Invoice Number]) / ATTR({FIXED [Category] : COUNTD([Invoice Number])})
3) Average Profit Margin = Average Gross Margin (they are used interchangeably)
https://public.tableau.com/profile/vinamra.mathur#!/vizhome/traige_1/Dashboard1
*Note - The twbx file is tableau desktop compatible.
Disclaimer: I am a beginner and trying to leverage Tableau for visual analytics skills in my job role.
Hello Mathur Vinamra - I am not trying to answer your query but attempting to learn from what you have already done.
Curious to understand in your "Quadrant" calculation why there is a repeat of IF & ELSEIF condition. Both seem to be same for me..How shoud i interpret it ??
IF [Average Bill Penetration] >= [Average Profit Margin] THEN "Quadrant 1"
ELSEIF [Average Bill Penetration] < [Average Profit Margin] THEN "Quadrant 2"
ELSEIF [Average Bill Penetration] >= [Average Profit Margin] THEN "Quadrant 3"
ELSEIF [Average Bill Penetration] < [Average Profit Margin] THEN "Quadrant 4"
END
