Sum the unique values and average repeating values for duplicated Purchase orders

[LynnM]

Hello,

I have been struggling with this and cannot find a solution online. My data has PO numbers and values that is linked to Jobs. As a result the PO numbers will repeat for every job linked to it. In most cases the repeating PO number has on assigned value so an average formula will correctly summarize the values. The problem is that when the values are not the same, then the average formula does not work.

The fomula I am using is 

PO FEE-Euro =

SUMX (

    VALUES ( 'Country Submissions'[PO fee Value-Euro] ),

    CALCULATE ( AVERAGE ( 'Country Submissions'[PO fee Value-Euro]) )

)

however PO ABCD total should be 278,375 but the formula averages the 68,250 rather than sum. I need a way that when a PO repeates and the PO value is the same to average the values but if a PO reapeats and the values are unique to sum. The results should be the sum of the avergae of repeating values attached to a repeating PO + the sum of unique values attched to a repeating PO. Thank you in advance. Below is an example of the data.

Local Job Number	Job Description (Job Title)	Client PO number	Client PO Fee amount	Client PO OOP amount
233714	Job 1	ABCD	48,225.00	62,500.00
233715	Job 2	ABCD	11,325.00	-
233716	Job 3	ABCD	68,250.00	15,416.67
233717	Job 4	ABCD	68,250.00	15,416.67
233718	Job 5	ABCD	45,365.00	83,333.33
233623	Job 6	ABCD	36,960.00	-
223360	Job 21	MNOP	66,000.00	-
223360	Job 22	MNOP	51,500.00	-
223361	Job 23	MNOP	51,500.00	12,857.14
223361	Job 24	MNOP	51,500.00	12,857.14
223362	Job 25	MNOP	51,500.00	-
223362	Job 26	MNOP	38,629.00	-
223363	Job 27	MNOP	61,815.00	71,428.57
223363	Job 28	MNOP	68,810.00	-
223365	Job 29	MNOP	44,709.00	-
223359	Job 30	MNOP	48,940.00	-
223365	Job 31	QRST	48,740.00	-
223366	Job 32	QRST	46,946.00	-
223359	Job 33	QRST	57,490.00	25,000.00
223368	Job 34	QRST	10,638.00	21,428.57
223364	Job 35	QRST	26,460.60	-
223367	Job 36	QRST	41,430.00	-
223248	Job 37	QRST	25,625.00	-
223248	Job 38	QRST	14,125.00	-
223248	Job 39	QRST	15,250.00	-
223248	Job 40	QRST	25,275.00	-
223248	Job 41	QRST	14,800.00	-

[devindupree01]

Hey Lynn,

Maybe over complicated but the Logic should be more like this

PO FEE-Euro =
VAR UniquePOs = SUMMARIZE('Country Submissions', 'Country Submissions'[Client PO number])
VAR Result =
SUMX(
UniquePOs,
VAR CurrentPONumber = [Client PO number]
VAR RelevantRows =
FILTER(
'Country Submissions',
'Country Submissions'[Client PO number] = CurrentPONumber
)
VAR UniqueValues = VALUES(RelevantRows[Client PO Fee amount])
RETURN
SUMX(
UniqueValues,
VAR CurrentValue = [Client PO Fee amount]
VAR CountOfCurrentValue =
CALCULATE(
COUNTROWS(RelevantRows),
RelevantRows[Client PO Fee amount] = CurrentValue
)
RETURN
IF(
CountOfCurrentValue > 1,
CurrentValue,
CurrentValue * CountOfCurrentValue
)
)
)
RETURN Result

If this helped, please mark as solved! 🙂

[LynnM]

I added the formula bu am getting the below formula error

[devindupree01 · Accepted solution]

Try this, a little simpler

PO FEE-Euro =
SUMX(
SUMMARIZE(
'Country Submissions',
'Country Submissions'[Client PO number],
"AvgOrSum",
IF(
COUNTROWS(
FILTER(
'Country Submissions',
'Country Submissions'[Client PO number] = EARLIER('Country Submissions'[Client PO number]) &&
'Country Submissions'[Client PO Fee amount] <> EARLIER('Country Submissions'[Client PO Fee amount])
)
) > 0,
SUM('Country Submissions'[Client PO Fee amount]),
AVERAGE('Country Submissions'[Client PO Fee amount])
)
),
[AvgOrSum]
)

[LynnM]

THANK YOU!!!!! This worked. I cannot tell you how many hours my team and I worked trying to figure this out. You have been heaven sent!

[Mkarwa-123]

@LynnM because you have average function inside calclculate.

[LynnM]

How do I correct? I am not great with formulas. Thanks