Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
orana
Helper I
Helper I

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#])

Column 2: TakingOver = if(CALCULATE(DISTINCTCOUNTNOBLANK('Sheet1'[AA or BB]),ALLEXCEPT(Sheet1,'Sheet1'[Enterprise Account Number]),'Sheet1'[FYTD Trace Units]>1)>1,"At Risk","Net New Accounts")

 

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. 

Capture1.PNGCapture2.PNG

 

Power BI File 

Excel File 

4 REPLIES 4
PaulDBrown
Community Champion
Community Champion

Please provide actual sample data (not an image)





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Updated my post with the power bi file and excel file

PaulDBrown
Community Champion
Community Champion

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.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Sure. The bar chart will have FYTD Trace Units  on the x-axis and enterprise account names on the y-axis (see example image below)

 

The fiscal year starts in Oct. 2020 and ends Sept. 2021. In the excel file, there is a column for fiscal month-year so you could just use that. 

Capture3.PNG

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.