Forum Discussion
Bar charts based on customer purchasing patterns
Hello,
I'm looking to create a 4 bar charts that are dependent on a customer's purchasing pattern in 2021 and 2020. I have two files. File 1 has customer's purchasing pattern across a plethora of products (names: a, b, c, d, e, f, g, h, i, etc.) in 2020 and 2021. File 2 assigns the manufacturer (AA or BB) per product name (a, b, c, etc.). File 2 is important as it list out products that are similar to each other, but manufactured by two different organizations. I've established a relationship between the two files via product name. This allows me to look at the purchasing patterns of customers based on the two different manufactures who make similar products. I have two columns with formulas and attached images of my data.
Column 1: AA or BB = LOOKUPVALUE('Sheet2'[Mfg AA or BB],'Sheet2'[catalog number],'Sheet1'[Material#])
Bar chart 1 - will only show only customer names that have purchase products manufactured by BB in 2021 and 2020
Bar chart 2 - will show customer names where the number of units purchased from manufacturer AA in 2021 is >50% of the units purchased from manufacturer BB in 2020 and the number of units purchased from manufacturer BB in 2021 is <45% of the units purchased from manufacturer in 2020
Bar chart 3 - will show customer names where the number of units purchased from manufacturer AA in 2021 is >90% of the units purchased from manufacturer BB in 2020 and the number of units purchased from manufacturer BB in 2021 is <5% of the units purchased from manufacturer in 2020
bar chart 4 - will only show customers who have purchased product manufactured by AA in 2021 and 2020
Below is an image of my data.
4 Replies
Please provide actual sample data (not an image)
Updated my post with the power bi file and excel file
Thanks for the sample files. When does the fiscal year end? Can you please create a mockup of the bar chart your are looking for in Excel and post here? I am not sure what the charts need to show.
