I want to calculate the total PO amount for each distinct PO number.

[Target]

I have two queries: one is a header-level query that includes PO number and PO amount columns, and the other is a GRN query that contains line details of the header-level query with respect to its GRN number and PO amount.
I want to calculate the total PO amount for each distinct PO number.
For POs with a single GRN line (one GRN number), the total PO amount is displayed correctly.
However, for POs with multiple GRN lines (multiple GRN numbers), the total is not showing the distinct value; instead, it sums up the entire PO amount column.

In filter totally 5 po's are there,
the first 2 PO's have 1 GRN line-so it shows total correctly,
Remaining PO has Multiple GRN lines,So it sums up all the lines,instead of distinct.
What is the correct dax formula.

the formula i have used here is
TotalPOAmountSweetWaterGRN =
SUMX(
DISTINCT(SweetWaterGRN[PO_NUMBER]),
CALCULATE(SUM(SweetWaterGRN[PO Amount]))
)

[Anonymous · Accepted solution]

Hi, @Target 

You can try the following methods.

TotalPOAmountSweetWaterGRN =
 Var _table=SUMMARIZE(SweetWaterGRN,SweetWaterGRN[PO_NUMBER],SweetWaterGRN[PO Amount])
RETURN
SUMX(_table,[PO Amount])

Is this the result you expected?

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.

[OktayPamuk80]

Hi gentlemen,

However, following best practices and trying to avoid comlicated measures, which might end with poor performance, isn't it better to handle this in power query as separate table as I stated before?

What is your opinion on that?

Thanks in advance,.
Regards,
Oktay

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected resut.  Share data in a format that can be pasted in an MS Excel file.

[OktayPamuk80]

Hi, the simplest way is to prepare this in advance in the power query. Just create a new table out of the existing one, including only the PO number and the amount, remove duplicates and connect it to the existing data model in the model view over the PO number. Then you can aggregate it without having to create complex measures.

Regards,
Oktay

Did I answer your question? Then please mark my post as the solution.

If I helped you, click on the Thumbs Up to give Kudos.

[MNedix]

Heya,

There must be something else in there which forces the formula to break because I replicated your case and it worked perfectly fine (see file attached).

Cheers,

[dharmendars007]

Hello @Target , 

Please try the below adjusted formula, Values() has been used to have distinct PO Nukebers.

TotalPOAmountSweetWaterGRN =
SUMX(
VALUES(SweetWaterGRN[PO_NUMBER]),
CALCULATE(SUMX(
VALUES(SweetWaterGRN[GRN_NUMBER]),SUM(SweetWaterGRN[PO Amount]))))

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN