Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hey guys. I’m new to Power BI and I’ve encountered a problem I can’t seem to fix. I’m not at liberty to post the actual numbers or a picture of the connections (everything should be connected though). I’ve got two different databases, which I’m pulling numbers from (see picture).
I got the following variables:
Tables:
Actual = revenue secured
Forecast = forecast
Measure:
Estimate = actual for the secured months and then forecast for the following months.
Formula for Estimate:
Estimate ($) = (if(sum(f_sales_forecast[Forecast])=0;sum(f_sales[Actual]);sum(f_sales_forecast[Forecast])))
The grand total only counts the forecasted period (49), and ignores the rest of the numbers that comes from Actual (100). What am I doing wrong?
Thanks for your help in advance
Solved! Go to Solution.
It seems you forgot a part in your formula. You wrote this one:
Estimate ($) = (if(sum(f_sales_forecast[Forecast])=0;sum(f_sales[Actual]);sum(f_sales_forecast[Forecast])))
But if you want the sum of Actual + Forcast, you should write something like:
Estimate ($) = (if(sum(f_sales_forecast[Forecast])=0;sum(f_sales[Actual]);sum(f_sales_forecast[Forecast])+sum(f_sales[Actual])))
and it will give you your Grand Total
Hello,
I recreated this using a simpler table, and i think you are not supposed to use sum on every step.
I got it working using the following formula:
Estimate = IF(Table2[Forecast] = 0;Table2[Actual];Table2[Forecast])
Make sure you change the table names.
If it doesn't work for you, or if you have any questions please ask.
Good luck
Hi @BeemsC
If i don't use the sum function I can't select the Tables for some reason.
Br
Pastrupgaard
It seems you forgot a part in your formula. You wrote this one:
Estimate ($) = (if(sum(f_sales_forecast[Forecast])=0;sum(f_sales[Actual]);sum(f_sales_forecast[Forecast])))
But if you want the sum of Actual + Forcast, you should write something like:
Estimate ($) = (if(sum(f_sales_forecast[Forecast])=0;sum(f_sales[Actual]);sum(f_sales_forecast[Forecast])+sum(f_sales[Actual])))
and it will give you your Grand Total
Hey @GregoryMartin
You're totally right and that gets me a lot closer to the issue, but I forgot about the last part of the problem. So the Estimate should consist of the [actual] up until the [forcast] kicks in. What my formula does now, is take both the [actual] and the [forcast] for month 11 and 12, which gives me a wrong estimate.
Right now my grand total for Estimate is 155 and i would like it to give me 149 (10 months actual and 2 months forecast)
I hope that made sense.
Br
Pastrupgaard
Yeap,
There weren't values on the first screen for [actual] on month 11 & 12. Now you've got some. I think, if you just want the [forecast] value in that case, you may create a new column in power BI using a formula looking like the first one you did.
NewColumn = if(f_sales_forecast[Forecast]=0;f_sales[Actual];f_sales_forecast[Forecast])
You'll have then a column with values for months 1 to 12 corresponding at your wish. Now you just need for your Grand Total cell to do a sum of this column. You should now have your 149 value.
I've tried a lot of different things and the problem with creating a new column is that i have to pull data from two different databases, so the number in the column is the same all the way down through the rows. Any bright ideas :D?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |