Need to sum based on unique values

[zeckert]

I have a Purchase order report that i need to be able to group by vendor but there is multiple rows for one PO due to line items.  There is a column that has the final amount but I keep getting it counted multiple times based on how many lines for that po there is.  I don't want to have to remove rows if i don't have to.  Sample Data listed below

I need vendor grouping with each PO# being counted once and add a total for each vendor.

Vendor	PO#	Order Amount	Inventory #	Invoice Date	Inventory Amt
Vendor1	284304	583.37
Vendor1	284304	583.37	S4228572.1	8/8/2017	338.44
Vendor1	284304	583.37	S4228572.2	8/8/2017	154.98
Vendor1	284304	583.37	S4228572.3	8/8/2017	90.36
Vendor2	284308	140.7
Vendor2	284308	140.7	S4228579.1	8/8/2017	140.7
Vendor1	284309	44.54
Vendor1	284309	44.54	S4228592.1	8/7/2017	44.54
Vendor2	284310	9.69
Vendor2	284310	9.69	S4228610.1	8/7/2017	9.9

[deepvibha]

Hi,
It is 'generally' recommended to 'unpivot' the Excel data while importing in Power BI. I have an Excel "Accounts Receivable" data maintained in the following "Outstandings" table:
Table "Outstandings"

Month

Entity

Debtor Group

Debtor Agency

A. 30 days & less

B. 30 to 60 days

C. 60 to 90 days

D. 90 to 180 days

E. 180 to 240 days

F. 240 to 365 days

G. 365 to 730 days

H. 730 to 1095 days

I. 1095 days & Greater

Apr-18

AAA

BBB

CCC

1000

200

500

255

1000

2000

600

480

550

After unpivoting and importing, it is like:

Month	Entity	Debtor Group	Debtor Agency	Age	Amount
Apr-18	AAA	BBB	CCC	A. 30 days & less	1000
Apr-18	AAA	BBB	CCC	B. 30 to 60 days	200
Apr-18	AAA	BBB	CCC	C. 60 to 90 days	500
Apr-18	AAA	BBB	CCC	D. 90 to 180 days	255
Apr-18	AAA	BBB	CCC	E. 180 to 240 days	1000
Apr-18	AAA	BBB	CCC	F. 240 to 365 days	2000
Apr-18	AAA	BBB	CCC	G. 365 to 730 days	600
Apr-18	AAA	BBB	CCC	H. 730 to 1095 days	480
Apr-18	AAA	BBB	CCC	I. 1095 days & Greater	550

The ER is 
Entity Table - 1 to many - Debtor Group Table
Debtor Group Table - 1 to many - Debtor Agency Table
Age  Table - 1 to many - Outstandings Table

There are 11 Entities, 12 Debtors and at least 18 Debtor Agencies in the dataset. Above is an example of One Month, for a single entity, debtor and debtor agency.
My query is how do I sum the amount so that I am able to slice it either/and by Entity, Debtor Group, Age?
Also what slicers should I have in my visuals?

Thanks
Deepak

[zeckert]

So the desired look i would want is listed below Vendor1 627.91 Vendor2 150.39 The order amount will always be the same because that is the final amount of the purchase order however there is multiple rows to account for each item in order. So it will read 583 at least once for the main line but read at least one other line for the item in the order. I need to be able to take the amount one time based on the PO # and sum all orders under vendor1 then same for vendor2 and so on

[Vvelarde · Accepted solution]

@zeckert

Hi,

Try with this Measure:

TotalOrderAmount = SUMX(DISTINCT(Table1[PO#]),CALCULATE(AVERAGE(Table1[Order Amount])))

Regars

Victor

Lima - Peru

[Anonymous]

This worked but still dont understand why we need to use AVERAGE instead of SUM.

[axelBose]

Thank you, Victor @Vvelarde Need to sum based on unique values .

I have uesd this same measure in one of my reports. However, when I reference it in the following, I do not get the correct output.

Upper bound =

VAR __AVG = sumX(distinct('Order History'[MaterialPlantKey]),calculate(average('Order History'[AvgNo0_Corrected Orders])))

VAR __STDEV = sumX(distinct('Order History'[MaterialPlantKey]),calculate(average('Order History'[StdDevNo0_Corrected Orders])))

VAR __UPPER_BOUND = __AVG + __STDEV *[Sigma level Value]

return ( __UPPER_BOUND)

In the above measure I am taking the distinct values by materialplant key for the average and standard deviation of orders in order to calculate the upper bound. The sigma level is simply the number of standard deviations that the user selects in the report with a slicer.

Thank you,
Axel

[NaveenVerma]

Hi Vvelarde,

Can we also use filter in the below formula?

TotalOrderAmount = SUMX(DISTINCT(Table1[PO#]),CALCULATE(AVERAGE(Table1[Order Amount])))

Regards,

Naveen Verma

[Anonymous]

Hi @Vvelarde, 
The Measure "TotalOrderAmount = SUMX(DISTINCT(Table1[PO#]),CALCULATE(AVERAGE(Table1[Order Amount])))" works perfectly but I can not seem to wrap my head around how it works. Could you provide an explaintation? 
Thanks

[Mosa]

Hi.

Thanks for your help. I have a question with the same context. Now, when I unpivot comlumns, data is repeated. So, I want to sum the number of values in one column, but it appreas as doubled

[zeckert]

Thank you so much.  It worked perfect.

[v-yuezhe-msft]

@zeckert ,

Do you get expected result when directly creating table visuals as follows? If not, please post you desired result.



Regards,
Lydia

[mattbrice]

If your image is a "before" pic, can you include an "after" (or what you want your sample data to look like)?