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.
Any help would be greatly appreciated!
Hoping to get the script right for the result? If you see the result by ID, for some reason it's not calculating the individual numerator over denominator values. It's just giving it a standard decimal by what seems ID aggregation? The script logic seems ok to me...
Solved! Go to Solution.
Thanks so much for your help Felix! Much appreciated!! I solved it using this: SUMX(VALUES('Table'[field]), CALCULATE(DISTINCTCOUNT('Table'[field])))
Hi @Aj24 ,
Looking at your data and the explanation you are providing the only questions is the blank values correct? What is the value you want to have instead of -1?
I have change the result formula in order to give 0
Result =
VAR _Cost =
SELECTEDVALUE ( 'Table'[Cost] )
VAR _act = [Actual]
VAR _forc = [Forecast]
VAR _targ = [Approved Target]
VAR _att =
IF (
ISBLANK ( _act ),
IF ( _Cost = "Forecast", DIVIDE ( _forc, _targ, 1 ) - 1 ),
IF ( _Cost = "Actual", DIVIDE ( _act, _targ, 1 ) - 1 )
)
RETURN
_att
You can set a alternative value on the divide when you have errors, in this case I added the 1 since you are subctrating 1.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsBig thanks so much @MFelix !! Super helpful! What I realised is that I think the formula is aggregating by ID and all the actual for the numerator and all the target values for denominators which is why it's providing a standard decimal for all the values in a certain time period.
When really I would like it to be filtered for each individual ID, year and month (so just each individual row). Do any ideas of appropriate filtering come to mind for this? Really appreciate all your support!!
Hi @Aj24
If I'm getting the model correctly, you don't have values for all the forecast lines that will make it difficult to get the calculation has you need.
I have changed your information to a matrix and has you can see you don't have always values for the forecast.
In your formula you are checking if actuals is blank and the first calculation you are making is the division by the forecast.
Can you please tell me in words what is the calculation you want to do? believe this is an issue with the formula itself maybe you need to start no by the actuals but by the forecast or the target.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks so much for your help Felix! Much appreciated!! I solved it using this: SUMX(VALUES('Table'[field]), CALCULATE(DISTINCTCOUNT('Table'[field])))
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 |
---|---|
113 | |
103 | |
76 | |
66 | |
63 |
User | Count |
---|---|
142 | |
105 | |
102 | |
81 | |
68 |