Forum Discussion
kellyylx
Helper I
Helper IChart visualization
Hi, I have this table in which I want to create some charts to track the progress cleaning the number of duplicated accounts. My initial thought is to plot a stacked column chart where it sh...
You could try a line / area chart of accounts vs duplicates plotted over time, and put the categories (countries) as small multiple
Or, potentially do a cumulative burndown chart, with bars showing closed vs new, lines showing accounts and duplicates or planned vs. actual clean-up
Hi kellyylx,
Thanks for reaching out to the Microsoft fabric community forum.
Coming to your query on community forum, Including the "Newly Created Accounts" column might be useful as a view into how the New Accounts dynamics affect the cleaning process for the duplicated accounts. Here are a few chart options that incorporate this data nicely, you can use the following approaches:
1. Combined Line and Stacked Column Chart
Purpose: Shows how newly created accounts and duplicates evolve together over time.
Insight: This highlights how new accounts contribute to changes in the total active accounts, possibly
influencing the duplicate percentage.
How to Design:
*) Use stacked columns for:
Unique accounts (total active accounts minus duplicates).
Duplicated accounts.
*) Overlay a line chart for newly created accounts.
*) The x-axis represents time.
Steps:
1. Prepare Your Data:
• Ensure your table has columns for:
• Date.
• Total active accounts.
• Duplicated accounts.
• Newly created accounts.
• Unique accounts (calculate as: Total Active Accounts - Duplicated Accounts).
• Organize data by time (e.g., daily, weekly, monthly).
2. Create a Data Series:
• Use these series:
• Unique accounts.
• Duplicated accounts.
• Optionally, add a separate line for newly created accounts.
3. Build the Chart:
• In your charting tool (Excel, Tableau, Power BI, etc.):
• Select the date column for the x-axis.
• Use stacked areas for unique and duplicated accounts.
• Optionally, overlay a line chart for newly created accounts.
4. Format the Chart:
• Use distinct but complementary colors for stacked areas.
• Add data labels and a legend.
• Include annotations for significant events.
2. Stacked Area Chart
Purpose: Visualizes the breakdown of account statuses over time.
Insight: This shows the proportion and trends of duplicates and new accounts relative to the overall growth.
How to Design:
*)Plot cumulative categories such as:
• Unique accounts.
• Duplicated accounts.
• Newly created accounts (separately or included in unique accounts).
*)Use the total count as the baseline.
Steps:
1. Prepare Your Data:
• Include:
• Date.
• Total active accounts.
• Duplicated accounts.
• Unique accounts.
• Newly created accounts.
2. Set Up the Chart:
• Use stacked columns for:
• Unique accounts.
• Duplicated accounts.
• Overlay a line chart for:
• Newly created accounts.
3. Build the Chart:
• Select a chart type that supports combo formats (e.g., Excel’s Combo Chart).
• Assign:
• Columns to the primary axis (stacked).
• Line chart (newly created accounts) to the secondary axis.
4. Format the Chart:
• Use clear colors for stacked columns.
• Distinguish the line chart with a bold color or dashed line.
• Add axis labels to clarify the different scales (total vs. newly created).
3. Waterfall Chart (Net Change in Accounts)
Purpose: Breaks down the total active accounts into components showing the impact of newly created accounts,
deleted accounts, and duplicate cleanups.
Insight: Visualizes the flow of account changes and highlights the impact of duplicate removal.
How to Design:
*) Show starting total active accounts.
*) Add steps for:
• Newly created accounts (positive step).
• Accounts deleted (negative step).
• Duplicate accounts removed (negative step).
• Ending total active accounts.
Steps:
1. Prepare Your Data:
• Include these components:
• Starting total active accounts.
• Newly created accounts.
• Accounts deleted.
• Duplicate accounts removed.
• Ending total active accounts.
2. Calculate Net Changes:
• For each step, calculate the incremental changes:
• Starting Accounts.
• Add Newly Created Accounts (positive value).
• Subtract Deleted Accounts (negative value).
• Subtract Duplicated Accounts Removed (negative value).
• Ending Accounts = Starting + Newly Created - Deleted - Duplicates Removed.
3. Build the Chart:
• In your charting tool:
• Select a waterfall chart type (Excel and Tableau have built-in options).
• Add columns for each component (e.g., "Starting Accounts," "Newly Created," etc.).
• Ensure the chart shows cumulative totals.
4. Format the Chart:
• Use consistent color coding (e.g., green for additions, red for subtractions).
• Add data labels for each step.
• Include gridlines or reference lines for clarity.
If this post helps then please consider accepting it as the solution to help the other members find it more quickly.
Thank you.