Forum Discussion
Helper IITotals Calculations using a % - a real problem
HI,
I have a calculation that's not working at the TOTAL level...it's fine at row level. I can see the problem and why it's happening but can't figure out a solution...hopefully a DAX Wizard will have an answer.
Below is a table of results. The BLUE totals are correct and would be the result if you had summed the figures in Excel.
The RED totals are the results from a Power BI table...as you can see the 'Adjusted by Probability' total is incorrect.
The total should be a sum of that column, whose figures are simply the 'Year 1 Authorised' figure multiplied by the 'Probabiliy'.
At a row level this calculation works fine.
I can see the problem for the Total is that the calculation is simply taking the sum of the 'Year 1 Authorised' and multiplying it by the sum of the 'Probability'...in this case 6.1...which is obviously wrong but understandable.
How to I fix this, I'm totally YouTube'd out!
This is the Measure I have created:
SUM ( Pipeline_MFMA[xDelChance_Number] )
)
| Project | Year 1 Authorised | Year 1 Adjusted by Probability | Probability |
| Scheme 1 | £116,356.31 | £58,178.16 | 0.5 |
| Scheme 2 | £68,799.15 | £41,279.49 | 0.6 |
| Scheme 3 | £35,205.69 | £7,041.14 | 0.2 |
| Scheme 4 | £18,367.85 | £11,020.71 | 0.6 |
| Scheme 5 | £0.00 | £0.00 | 0.8 |
| Scheme 6 | £0.00 | £0.00 | 0.8 |
| Scheme 7 | £0.00 | £0.00 | 0.6 |
| Scheme 8 | £0.00 | £0.00 | 0.8 |
| Scheme 9 | £0.00 | £0.00 | 0.6 |
| Scheme 10 | £0.00 | £0.00 | 0.6 |
| Correct | £238,729.00 | £117,519.50 | |
| Power BI Totals | £238,729.00 | £1,456,246.00 | 6.1 |
Any help would be a great Christmas present 😁
Thanks
Paul
Hi,
Try this measure
Measure = SUMX(VALUES(Pipelime_MFMA[Project]),[Year 1 Adjusted by Probability])
Hope this helps.
20 Replies
- MFelix
Super User
Hi Otto_Luvpuppy ,
You need to use a SUMX in order to make the correct calculation of the value try the following measure:
Year 1 Adjusted by Probability = VAR _Chance = CALCULATE ( SUM ( Pipeline_MFMA[xDelChance_Number] ) ) VAR _Authorised = CALCULATE ( SUM ( Pipeline_MFMA[xCPY1] ) - [CPY1_DEL_Unauthorised], Pipeline_MFMA, Pipeline_MFMA[xDevDel_CPY1] = "Delivery" ) RETURN SUMX(VAlUES(Table[Project],_Authorised * _Chance)- Otto_Luvpuppy
Hi Miguel,
Many thanks for your reponse...I'm trying to implement it now but can't see where the Table[Project] comes from, in the final line, or what to replace it with.
Paul
- MFelix
Hi Otto_Luvpuppy,
The table[project] is the column you present on your data that has the values Scheme 1, Scheme 2 and so on.
Since I did not know the name of the table and column place that generic name, you should replace by the column on your model regarding that Shecme values.
- Ashish_Mathur
Hi,
Try this measure
Measure = SUMX(VALUES(Pipelime_MFMA[Project]),[Year 1 Adjusted by Probability])
Hope this helps.
- Otto_Luvpuppy
Hi Ashish,
Thanks for your repsonse.
I'm not quite sure how this would work as the final part of your measure references itself...so doesn't that make it a circulare reference?
Paul
- Ashish_Mathur
Mine is a new measure.
askpbiuserHelper I
Hi Ashish_Mathur - I had the same issue and this solution gives me the right result. However the Project field in my case is a field parameter. For example my measure is -
Measure Final =
var _total =
IF(HASONEFILTER(WD_Dim[L3]),[Measure Interim],
SUMX(VALUES(WD_Dim[L3]),[Measure Interim]))
return _total
This gives right result, but based on field paramater it can be L3, L2 field etc from the WD_Dim table , so how do i tweak the above measure to get that result?- MFelix
Hi Otto_Luvpuppy ,
Currently you cannot use Field parameters to change your table dynamicaly, only option would be to do SUMX for each of the values in the field parameters.
