cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Highlighted
Vvelarde Super Contributor
Super Contributor

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!




View solution in original post

9 REPLIES 9
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
Highlighted
Vvelarde Super Contributor
Super Contributor

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!




View solution in original post

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

NaveenVerma Visitor
Visitor

Re: Need to sum based on unique values

Hi

 

Can we also use filter in the below formula?

 

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

 

Regards,

Naveen Verma

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors