cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
zeckert Frequent Visitor
Frequent Visitor

Need to sum based on unique values

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.

 

VendorPO#Order AmountInventory #Invoice DateInventory Amt
Vendor1    284304583.37   
Vendor1    284304583.37S4228572.18/8/2017338.44
Vendor1    284304583.37S4228572.28/8/2017154.98
Vendor1    284304583.37S4228572.38/8/201790.36
Vendor2     284308140.7   
Vendor2     284308140.7S4228579.18/8/2017140.7
Vendor1    28430944.54   
Vendor1    28430944.54S4228592.18/7/201744.54
Vendor2     2843109.69   
Vendor2     2843109.69S4228610.18/7/20179.9
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Need to sum based on unique values

@zeckert

 

Hi,

 

Try with this Measure:

 

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

 

Regars

 

Victor

Lima - Peru




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




8 REPLIES 8
mattbrice Senior Member
Senior Member

Re: Need to sum based on unique values

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

Moderator v-yuezhe-msft
Moderator

Re: Need to sum based on unique values

@zeckert ,

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


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
zeckert Frequent Visitor
Frequent Visitor

Re: Need to sum based on unique values

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
Super User
Super User

Re: Need to sum based on unique values

@zeckert

 

Hi,

 

Try with this Measure:

 

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

 

Regars

 

Victor

Lima - Peru




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




zeckert Frequent Visitor
Frequent Visitor

Re: Need to sum based on unique values

Thank you so much.  It worked perfect.

Mosa Occasional Visitor
Occasional Visitor

Re: Need to sum based on unique values

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

deepvibha Regular Visitor
Regular Visitor

Re: Need to sum based on unique values

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"

MonthEntityDebtor GroupDebtor AgencyA. 30 days & lessB. 30 to 60 daysC. 60 to 90 daysD. 90 to 180 daysE. 180 to 240 daysF. 240 to 365 daysG. 365 to 730 daysH. 730 to 1095 daysI. 1095 days & Greater
Apr-18AAABBBCCC100020050025510002000600480550


After unpivoting and importing, it is like:

MonthEntityDebtor GroupDebtor AgencyAgeAmount
Apr-18AAABBBCCCA. 30 days & less1000
Apr-18AAABBBCCCB. 30 to 60 days200
Apr-18AAABBBCCCC. 60 to 90 days500
Apr-18AAABBBCCCD. 90 to 180 days255
Apr-18AAABBBCCCE. 180 to 240 days1000
Apr-18AAABBBCCCF. 240 to 365 days2000
Apr-18AAABBBCCCG. 365 to 730 days600
Apr-18AAABBBCCCH. 730 to 1095 days480
Apr-18AAABBBCCCI. 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

Em-Dem Frequent Visitor
Frequent Visitor

Re: Need to sum based on unique values

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