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 shows the percentage of duplicated accounts against the total number of active accounts count. However, it is possible that new accounts are being created every day or accounts being deleted which will cause the percentage to change even though the number of duplicated accounts is the same.
Is there a visualisation/chart i can use to involve Newly Created Acc column in my analysis?
Hi @kellyylx,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hi @kellyylx,
We haven’t heard back from you for some time, so just following up to our previous message. We hope your issue has been resolved.
If the solution we provided has worked for you, kindly mark it as the accepted solution. Your feedback is important to us, Looking forward to your response.
Thank you for being a valued member of the Microsoft Fabric Community Forum!
Hi @kellyylx,
As we haven’t heard back from you, so just following up to our previous message. We hope your issue has been resolved.
If the solution we provided has worked for you, kindly mark it as the accepted solution. Your feedback is important to us, Looking forward to your response.
Thank you for being a valued member of the Microsoft Fabric Community Forum!
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.
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
It is expected, a change in either of aggregations used in the stacked column charts will change the percentage of one to the total of both. So, if initially, active account is 2 and duplicate is 4, that would be 2/6 and 4/6 respectively. If active then becomes 1 and 4 remains the same, that would 1/5 and 4/5 respectively.
Unless you mean something else.
Hi, the duplicate count will always be smaller than the active count because I am counting the number of duplicates withint the active accounts.
I am wondering if there is a better visualisation/chart i can use that involves th Newly Created Acc Count column in the analysis of tracking the number of duplicates in each country
I dont think it's another viz that you need but rather getting the correct measures. If you could provide a workable sample data (not an image) and your expected result from that and the reasoning behind, I might be able to whip up some DAX measures for you.
