PowerBI DAX distinct sum

[MINGXIN]

Dear,

Hope you are well.

I have a dataset as below. I'm trying to create a formula to calculate the complete_qty corresponding to each order and then sum it up (

18.8309393

 from 2023090402R

+

14.1792

 from 2337019R

).Because Category 1 under each order should have the same complete_qty.
So overall, complete_qty should be summed up for each order.

Thanks for any ideas.

BR

Soldier

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[MINGXIN]

Hi lbendlin，

Thanks for your reply, please find the dataset below.

Thank you so much for your attention.

[lbendlin]

like this?

[MINGXIN]

Hi @lbendlin ,

Hope you are well. I tried, but there's a slight difference when there is the same qty in multiple orders, the final summary is wrong.

[MINGXIN]

Thanks Bro @lbendlin 

[MINGXIN]

Hi @lbendlin ,

Thanks for your prompt action. The target table is as below.(FYI-in the report we actually only selected two orders (4 out of all), so it should be 33.01, not a summary of 4).

Thanks in advance.

Soldier

[lbendlin]

That seems to match my version?

[MINGXIN]

Hi @lbendlin 

I think I got the solution.  FYI-Solved: Grand Total ALL() with filter - Microsoft Fabric Community

完工数量_new =

//sumx(Values('Fact_QTY_Cost'[产成品订单号]) , calculate(distinct('Fact_QTY_Cost'[完工数量])))

Var _product = ALLSELECTED('Fact_QTY_Cost'[产成品订单号])

Var _SUTYPE = ALLSELECTED('Fact_QTY_Cost'[CATEGORY1])

Return

CALCULATE(sumx(Values('Fact_QTY_Cost'[完工数量]) , calculate(distinct('Fact_QTY_Cost'[完工数量]))),ALL('Fact_QTY_Cost'),'Fact_QTY_Cost'[产成品订单号] IN _product,'Fact_QTY_Cost'[CATEGORY1] IN _SUTYPE)

[MINGXIN]

May not. For new columns/metrics, the unique values should be aggregated for the currently selected orders(18.8309393+14.1792)

[MINGXIN]

• order_no	component_part_no	complete_qty	Category1
  2023090402R	181929	18.83094	SOLVENT
  2023090402R	180419	18.83094	SOLVENT
  2023090402R	180699	18.83094	SOLVENT
  2337019R	180196	14.1792	SOLVENT
  2023090402R	213696	18.83094	PACKAGING
  2023090402R	226713	18.83094	PACKAGING
  2337019R	221817	14.1792	PACKAGING
  2337019R	221808	14.1792	PACKAGING
  2337019R	221807	14.1792	PACKAGING
  2337019R	221541	14.1792	PACKAGING
  2337019R	221488	14.1792	PACKAGING
  2337019R	221808	14.1792	PACKAGING
  2337019R	229054	14.1792	Others
  2337019R	229054	14.1792	Others
  2023090402R	182316	18.83094	INK
  2023090402R	185773	18.83094	INK
  2023090402R	1154252	18.83094	FILM
  2023090402R	1154252	18.83094	FILM
  2023090402R	1131288	18.83094	FILM
  2337019R	1139379	14.1792	FILM
  2337019R	1139379	14.1792	FILM
  2337019R	1138440	14.1792	FILM
  2337019R	1138440	14.1792	FILM
  2337019R	186006	14.1792	ADHESIVE
  2337019R	185871	14.1792	ADHESIVE