Sumx and Value is not working for sort column

[ngocnguyen]

Hi

I have 2 column below

table 1 is raw data

I created  Dimdate table and table 2 , then create relationship amount Table 1, table 2, dimdate table

In table 2, I sort " Zone" by "order" column to fix the order of zone

I create below measure

Sales = sum( table 1[sales])

M3 =sum( table 1[M3])

Sales/M3=divide([sales],[M3],0)

Sales/M3 LY=calculate([sales/M3],dateadd(Dimdate[Date],-1,year))

Sales LY= calculate([sales],dateadd(Dimdate[Date],-1,year))

%sales LY=DIVIDE([Sales LY],CALCULATE([Sales LY],ALL(table2[Zone])),0)

New sales =[% sales LY]*CALCULATE([Sales],ALL(table2[Zone]))

New M3 =SUMX(VALUES(table2[Zone]),DIVIDE([New sales],[Sales/M3 LY],0))

All measures were run well, except for "New M3" measure. the result should be like the table "output".  

Is there any mistake that I made for "New M3" measure ?, Pls help me to solve it.  I think the problem because I sort " Zone" column by "order" column in table 2?

[dedelman_clng]

Hi @ngocnguyen -

This should have nothing to do with it being a sort column. You might want to check your math for New M3, however. Based on your code, it should be 3/0.5 = 5 (4.5 rounded), 4/0.6 = 7 (6.67 rounded) and 5/0.666... (rounded to .7 in your visual) =  8 (7.5 rounded up).  

Other than not sure how to help - are you getting an error message with your code? If not, what values are you getting? And please provide a picture at your data model so we can determine if that might be the cause.

Hope this helps,

David