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 |
Solved! Go to Solution.
Hi,
Try with this Measure:
TotalOrderAmount = SUMX(DISTINCT(Table1[PO#]),CALCULATE(AVERAGE(Table1[Order Amount])))
Regars
Victor
Lima - Peru
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
Hi,
Try with this Measure:
TotalOrderAmount = SUMX(DISTINCT(Table1[PO#]),CALCULATE(AVERAGE(Table1[Order Amount])))
Regars
Victor
Lima - Peru
This worked but still dont understand why we need to use AVERAGE instead of SUM.
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.
Hi Vvelarde,
Can we also use filter in the below formula?
TotalOrderAmount = SUMX(DISTINCT(Table1[PO#]),CALCULATE(AVERAGE(Table1[Order Amount])))
Regards,
Naveen Verma
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
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
Thank you so much. It worked perfect.
@zeckert ,
Do you get expected result when directly creating table visuals as follows? If not, please post you desired result.
Regards,
Lydia
If your image is a "before" pic, can you include an "after" (or what you want your sample data to look like)?
Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.
Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!
User | Count |
---|---|
380 | |
102 | |
69 | |
57 | |
48 |
User | Count |
---|---|
329 | |
112 | |
87 | |
76 | |
61 |