Forum Discussion
Solved
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 co...
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