Hi, I am an existing Powerpivot user trying to get familiar with Power BI and am trying to convert my Powerpivot models over to BI. I have encountered an issue with a formula though - it is designed to give me forecast bias for sales. The first part works correctly:
This gives me in a table for each forecast month my sales bias. However, I have in my pivot model a formula that gives me a grand average at the bottom of my table and this does not seem to work in BI - I need the average of the bias calculation iterated over the months. My formula in powerpivot was:
If you have some images also that would be helpful of how this looks currently in the table. Also the model, especially the tables that calculations are over.
One thing is that you definitely should avoid placing the measure table name before measures. No need to do this.
Once I see this in a table.
Below I’ve looked to give you what I believe is likely the technique you need to use.
Thanks - the example you gave absolutely does what I am looking to achieve but I cant get it to work in my table. I will try to demonstrate. I have two fact tables, one with sales info called ‘Combined Detail’ and one with historical forecast information called ‘S&OP History’. These are separate sources and combining them I feel would be problematic - perhaps this is where I cannot get the above to work.
Here is the first part of the bias calc which works and my layout:
As you can see the individual S&OP months work out the bias as I want but the total sums up the total forecast and total sales to give an aggregated bias - this is what I would like to be the average so in this case it should be 0.76. My layout is below:
Please let me know if any additional info is required.
Would it be possible to summarize this down to say 2-3 months. Just so it’s easier to audit the numbers.
Any reason your dates are so out of order?
The reason it’s not reflecting the same is because the HASONEVALUE is doing nothing in your table, because the context is not (Month & Year) it’s being create by numerous date columns.
Within the has HASONEVALUE(…) place the S&OP Month column instead and see how that changes up the results.
Thanks Sam - I have tried what you recommended and still no nearer. The S&OP month in the wrong order - I haven’t looked into this yet, quite possibly because it doesn’t see these as dates, they are text, for this part I figured it wouldn’t matter. Below is a shorter version of my new table, you will see my total for ‘Bias Measure’ is 0.6 when it should be 0.74 as an average:
Thanks Sam - I really cant fathom it, I thought moving my DAX formulas over from Powerpivot and converting them would be the easy part!! How best can I show you the PBIX file?
Because it sits here within your model. With how your relationships are setup there is no way for any filter to reach across to the calculations that are in the table