By using this website, you agree to our Terms of Use (click here)
I have a Power BI I am trying to create and use as a dashboard in Acumatica that manages customer count increase/decrease year over year. Does anyone know how to achieve a CountIf function or something of the sort to show me the three areas I am trying to measure:
Sales in both years
Sales in 2017 none in 2018
Sales in 2018 none in 2017
Below is an example of 4 columns I currently have:
CustomerID 2017 Totals 2018 Totals Total Amount
123 1,000.00 1,500.00 2,500.00
456 500.00 500.00
789 1,000.00 1,000.00
Thank you in advance!!!
Are you using a separate table for Dates? It's a good idea to. In that table, have a column for the Year.
Then drag Year into a table and add a measure like this:
CustomerCount = DISTINCTCOUNT(Sales[Customer])
That would give you number of customers with sales in each year.
Kristi,
Take a look at the material included in the course call Microsoft: DAT206x - Analyzing and Visualizing Data with Excel in edx.org. You can take the course for free as long as you're just looking for knowledge. Look specifically at Module 5 - Using Advanced Formulas. This will show you how to create measures in Power Query that you can then leverage in your Power BI visualizations. They cover DAX functions like SAMEPERIODLASTYEAR, PREVIOUSQUARTER, and TOTALYTD functions that might help you tackle some of your objectives.
Edx also has a course Analyzing and Visualizing Data with Power BI.
Hope this helps. If it is too rudimentary, maybe the reference will help someone else looking to learn PowerQuery and Power BI.
Cheers!
Thank you Tim and Shawn. Adding the date table did help but my formulas seem clunky and not sustainable. It works for now but I can see issues down the road. This did help me resolve the issues I ran into with circular dependencies. I am much better off than I was though.
Shawn, thank you for the tutorial references. I'm sure it will not be too rudimentary. Even with the most entry-level, basic tutorials I have watched, I find myself picking up helpful bits of information.
Surely you just need a basic query of Customer Sales by date and sale amount (ie. Customer, Date, Sale Amount) then you use that with Power BI and ask it to show Sale Amount by Customer by Year and it will do the rest. Then mess around with the visualization options as needed, use a table or graph (with data labels).
I've recently started using Power BI and it is amazing.
In addition to the material that Shawn mentioned, I also wanted to mention the team at PowerPivotPro:
https://powerpivotpro.com/a-new-kind-of-company/
Writing measures in DAX can make your head spin and sometimes it's nice to engage with a consultant for an hour or two.