Forum Discussion

soldier_rong's avatar
soldier_rong
Icon for Helper II rankHelper II
2 years ago

DAX Distinct sumx the value is wrong in total line

Dear Team,

 

I am using CALCULATETABLE, CALCULATE, SUMX to calculate the Category classification under a particular month and I find that the value of TOTAL is not correct?

Thank you so much for your attention and participation.

 

BR

Soldier

______________________________________

DAX:

QTY_Byorder = 
var t =calculatetable(
DISTINCT(SELECTCOLUMNS('Fact_QTY_Cost'
, "order_no",[orderno]
, "complete_qty",Fact_QTY_Cost[complete_qty] ))
)
Return
CALCULATE(sumx(t,[complete_qty]) 
)
 
 
PBI result:

 

 

 

Sample data:

Monthorder_nomaterial_nocategory1complete_qty
2023092278346R1141726FILM14.04
2023092278346R1141726FILM14.04
2023092278346R1141726FILM14.04
2023092278346R224061FILM14.04
2023092278346R224105PACKAGING14.04
2023092278346R294510Others14.04
2023092281168R141050PAPER14.04
2023092281168R141052PAPER14.04
2023092281168R141053PAPER14.04
2023092281168R141053PAPER14.04
2023092281168R224061FILM14.04
2023092281168R224105PACKAGING14.04
2023092281168R224136FILM14.04
2023092281168R297849PACKAGING14.04
2023092295207R1149998FILM13.9536
2023092295207R1149998FILM13.9536
2023092295207R1149998FILM13.9536
2023092295207R1149998FILM13.9536
2023092295207R1149998FILM13.9536
2023092295207R1149998FILM13.9536
Need Help

5 Replies

    • soldier_rong's avatar
      soldier_rong
      Icon for Helper II rankHelper II
      2 years ago

      Hi ThxAlot ,

      Thank you so much for your attention and participation.

      When I tried to select 2 order in the list, but seem the result still not worked.

      You can see that the complete_qty of those two orders are both 14.04. So...

       

      Thanks in advance.

       

       

       

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    2 years ago

    Hi, soldier_rong 

     

    You can try the following methods.

    Measure = CALCULATE(SUM('Table'[complete_qty]),ALLEXCEPT('Table','Table'[order_no]))

    Is this the result you expect? Please see the attached document.

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • soldier_rong's avatar
      soldier_rong
      Icon for Helper II rankHelper II
      2 years ago

      Hi v-zhangti ,

       

      Thanks Bro. The complete_qty is a unique value for each order, for example 14.04.

       

      My target style is showing by category1, so for each month his complete_qty is the same (complete_qty for each order is added up, for example, in September there is order1, complete_qty is 5, order2, complete_qty is 6, order3, and it's complete_qty is 7, so each value of category1 under September is 18: 5+6+7) because it has all those orders for the month .

      That's why I expect the summarized values for the month to be to be consistent up and down (because the complete_qty is the same for the month).

       

       

      BR,

      Soldier