SUMX not working as intended

[Anonymous]

Hello,

I have a matrix with 3 levels: Lane (shipping route), Shipment #, Material.

I want to show total # of loads on each Lane, and then show the # of loads below 85% utilization for that Lane.

I have a column called '<85%' that is either 1 or 0, based on utilization of each shipment #. I can't just use SUM on that column, since I have another level in the matrix (material), causing the shipment # to have as many records as there are unique material on that truckload (see the '<85%' column). I tried using MAX and SUMX, which is working at the shipment # level, but the aggregation for the Lane-level is not working. It should be 5, not 2. Sorry if this is confusing. Any help is appreciated!

test = IF(ISINSCOPE('Pivot Data'[Shipment Number]),

            MAX([< 85%]),

            SUMX(VALUES('Pivot Data'[< 85%]), MAX('Pivot Data'[< 85%])))

[amitchandak · Accepted solution]

@Anonymous , Not very clear. It should be like

test = Sumx('Pivot Data'[Shipment Number]),calculate(MAX([< 85%])))

[Anonymous]

@amitchandak thank you, this worked! I still needed to use IF(ISINSCOPE(... but your solution was very helpful. 

Can you explain why I needed to use CALCULATE before MAX, rather than just using max? Thanks!