DAX Distinct sumx the value is wrong in total line

[soldier_rong]

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:

Month	order_no	material_no	category1	complete_qty
202309	2278346R	1141726	FILM	14.04
202309	2278346R	1141726	FILM	14.04
202309	2278346R	1141726	FILM	14.04
202309	2278346R	224061	FILM	14.04
202309	2278346R	224105	PACKAGING	14.04
202309	2278346R	294510	Others	14.04
202309	2281168R	141050	PAPER	14.04
202309	2281168R	141052	PAPER	14.04
202309	2281168R	141053	PAPER	14.04
202309	2281168R	141053	PAPER	14.04
202309	2281168R	224061	FILM	14.04
202309	2281168R	224105	PACKAGING	14.04
202309	2281168R	224136	FILM	14.04
202309	2281168R	297849	PACKAGING	14.04
202309	2295207R	1149998	FILM	13.9536
202309	2295207R	1149998	FILM	13.9536
202309	2295207R	1149998	FILM	13.9536
202309	2295207R	1149998	FILM	13.9536
202309	2295207R	1149998	FILM	13.9536
202309	2295207R	1149998	FILM	13.9536

[v-zhangti]

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]

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

[MINGXIN]

good

[ThxAlot]

[soldier_rong]

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.