Forum Discussion
Improper Sum with slicer
I have a table with shipping points, dates and total resources. I have used measures to calculate the count of dates the sum of resources.
The expected result is to calculate the sum of resources by count of date. The slicer is intended drill down to the shipping point level, but show the grand total when a slicer is not used. The slicer works correctly with Res/Days, but not the grand total when the slicer is not used. The intended result when the shipping point slicer is not selected is 321 Res/Days but it is calculating 153. Any help with this would be greatly appreciated.
| Shipping Point | Sum of Tot Res | Count of Dates | Res/Days |
| 1068 | 171 | 19 | 9 |
| 1070 | 120 | 20 | 6 |
| 1073 | 159 | 20 | 8 |
| 1074 | 757 | 25 | 30 |
| 1076 | 281 | 20 | 14 |
| 1078 | 360 | 22 | 16 |
| 1125 | 908 | 20 | 45 |
| 1126 | 48 | 20 | 2 |
| 1127 | 601 | 20 | 30 |
| 1129 | 946 | 20 | 47 |
| 1131 | 381 | 20 | 19 |
| 1132 | 500 | 20 | 25 |
| 1135 | 214 | 21 | 10 |
| 1138 | 519 | 20 | 26 |
| 1164 | 499 | 20 | 25 |
| 1185 | 147 | 20 | 7 |
Please try the measure logic below:
Total Res = SUM ( Fact[Total Resources] ) Days = DISTINCTCOUNT ( Fact[Date] ) Res/Days = VAR _perPoint = SUMX( VALUES( Fact[Shipping Point] ), DIVIDE( CALCULATE( [Total Res] ), CALCULATE( [Days] ) ) ) RETURN _perPoint
3 Replies
Hi,
I am not sure about how the measures are written, but please try something like below with the two measures.
expected result measure = SUMX ( VALUES ( Data[Shipping Point] ), DIVIDE ( [Sum to total resources:], [Count of days] ) )Please try the measure logic below:
Total Res = SUM ( Fact[Total Resources] ) Days = DISTINCTCOUNT ( Fact[Date] ) Res/Days = VAR _perPoint = SUMX( VALUES( Fact[Shipping Point] ), DIVIDE( CALCULATE( [Total Res] ), CALCULATE( [Days] ) ) ) RETURN _perPointThank you! I used the measure in conjunction with my sum & count measure and it worked!
