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.
i everyone,
I am trying to calculate the total for the field result of the following table:
Year | Quarter | Line | Total Days | Average Days | Total Items | % Effectiveness | Result |
2016 | 3 | ACA | 128 | 128 | 1 | 0.05 | 6,4 |
2016 | 3 | HIL | 1016 | 508 | 6 | 0.3 | 152,4 |
2016 | 3 | INV | 1989 | 994,5 | 10 | 0.5 | 497,25 |
2016 | 3 | TEJ | 528 | 264 | 3 | 0.15 | 39,6 |
Grand Total | 3661 | 473,625 | 20 | 1 | 473,625 |
The right value I expect is 695.65.
But DAX is not adding the fields (6.4 + 152.4 + 497.25 + 39.6), instead it is multiplying the total fields Average Days (473,625) and [% Effectiveness] (1) . I created the following Measures to perform this calculation:
Average Days = AVERAGE(Table[Total Days])
% Effectiveness = SUM(Table[Total Items]) / CALCULATE(SUM(Table[Total Items]),ALLSELECTED(Table))
Result = Table[Average Days] * Table[% Effectiveness]
I appreciated your help.
Solved! Go to Solution.
Hi, Try with this DAX
Result = SUMX ( SUMMARIZE ( Table1; Table1[Line]; "RESULTS"; [AverageDays] * [% Effectiveness] ); [RESULTS] )
Review Your Column in Summarize accord to your visual & Structure.
You need to use SUMX(table,[Result]).
The functions ending in X are iterator, which in lay-mans terms means they will loop through the rows in the current filter context calculating the result for each row before performing the final aggregation.
Thanks for your answer, but using SUMX is the same behavior, the real problem is that it applies the calculation even in the totals, it only adds the previous records only when they come from a defined table or column, not measures, because this applies the calculation for each row.
Item | Average Days | % Effectiveness | Result with DAX |
1 | 128 | 0,05 | 128 * 0,05 = 6,4 |
2 | 508 | 0,30 | 508 * 0,30 = 152,4 |
3 | 994,5 | 0,50 | 994,5 * 0,50 = 497,25 |
4 | 264 | 0,15 | 264 * 0,15 = 39,6 |
Grand Total | 473,625 | 1 | 473,625 * 1 = 473,625 |
Result = Table[Average Days] * Table[% Effectiveness]
I need another way to calculate this Result and being able to get the right value: 695,65
Thanks!!
Hi, Try with this DAX
Result = SUMX ( SUMMARIZE ( Table1; Table1[Line]; "RESULTS"; [AverageDays] * [% Effectiveness] ); [RESULTS] )
Review Your Column in Summarize accord to your visual & Structure.
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 |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |