Forum Discussion
Helper INeed 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.
| 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 |
11 Replies
mattbriceSolution Sage
If your image is a "before" pic, can you include an "after" (or what you want your sample data to look like)?
zeckert ,
Do you get expected result when directly creating table visuals as follows? If not, please post you desired result.
Regards,
Lydia
zeckertSo 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 onHi.
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
deepvibhaAdvocate II
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
