Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello everybody, im new to the community and excited to share some thougths, in this case I'm looking for some help.
The bottom line is that im looking to do a monthly forecast for the current and the next 2 months, since the product is price sensitive, I wanted to calculate an estimate base volume for the sales forecast, to do so I was thinking to stablish a future price that I would project and given this price then the returning values would be:
Not sure how im displaying the estimate price either, it can be just a table with prices I write down or can be calculated and adjusted with a what if parameter. So lets say there is a price of 10$, then it would look for the MAX & MIN total of historic sales in a month and bring back the volume sold of that month with the range of price 9.85$-10.15$ for example.
So once I have this MAX & MIN values I could use different parameters of calculation to stirr the forecast in the right direction, since im expecting that even with same prices there has been different behavior in sales.
My data set for this consist of 3 table: Calendar table, Product table and all historic sales data table.
The data table has all orders information (data, price, volume, product number, customers etc) and connects with the other 2 tables I mentioned before by date and product number.
My end result should be displayed in a matrix visualization with something like:
The result in the 2 columns could of been the total sales in September of 2015, August 2016 or any other month of any other year for that product.
After I would just make 3 calculations and estimate current month, next month and "in 2 month" forecast.
I have found very insightful material in this forums but nothing quite like this as I need the sum of the month that matches the pricing parameters for a specific product. I would much appreciate any or all help the community can offer to set me in the correct direction.
Regards,
DB
Solved! Go to Solution.
Hello @Greg_Deckler I had problem pasting data (it wouldnt upload anything I pasted in a format where you could copy/paste and have fast access to the data) combined with a poor internet is the result. Thank you for the link regarding the proper way to upload and present the information.
I followed many of the steps and tried to convert some parts of your post (https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...) that I found on this forums and came to solve my original problem, however now im facing a diferent problem, BUT will try to investigate some more and see if I can solve it.
Thank you for the replies!
Regards,
Daniel B
You may try using SUMMARIZE Function inside the MINX Function and MAXX Function
Hello Sam,
You did get me thinking because at first I didnt see it, a couple of hours later it hit and seems like the way to go, but still I couldnt find a way to make it work, given the previous exmaple posted, what do you suggest? Between today and tomorrow ill keep on working on it and will update.
Regards,
Daniel B
Ive tried multiple ways with the formula I came up with. For the moment im ignoring the fact that there is a price consideration, Just attempting to bring back the Max volume from any months in the history of the company for a certain product.
One code that generate something close of what im looking for is:
Max = MAXX(Sales, SUMX( SUMMARIZE( Sales, 'Product'[Consolidated Items Desc], "Monthly Volume",[Volume Sold all pounds] ), [Monthly Volume] ) )
But the result it yields are not what I expect. I use 2 tables to verify the information, the first one has year,month and volume displaying the information of volume by month (with subtotals in year and total for all years, picture attached) and the another table with product name and the MAX value im looking for. In this example im expecting a 56k Volume (from June 2015) but instead it brings me the total value below for all years 2.120.220, which is in fact the MAX for the measure I calculated with this context.
Anybody able to give me some support with this please? Let me know if you need aditional information, I will provide if I can.
Regards,
Daniel B
@DanielButto- Been on vacation, just getting back. Can you post sample source data in a format that can be copied and pasted? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Hello @Greg_Deckler I had problem pasting data (it wouldnt upload anything I pasted in a format where you could copy/paste and have fast access to the data) combined with a poor internet is the result. Thank you for the link regarding the proper way to upload and present the information.
I followed many of the steps and tried to convert some parts of your post (https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...) that I found on this forums and came to solve my original problem, however now im facing a diferent problem, BUT will try to investigate some more and see if I can solve it.
Thank you for the replies!
Regards,
Daniel B
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |