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|
Solved! Go to Solution.
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:
|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|
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|
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?
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.
Can we also use filter in the below formula?
TotalOrderAmount = SUMX(DISTINCT(Table1[PO#]),CALCULATE(AVERAGE(Table1[Order Amount])))
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 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
Do you get expected result when directly creating table visuals as follows? If not, please post you desired result.
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!
Join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!
Click here to read more about the May 2022 updates!