Forum Discussion
Income Statement Matrix
Hello, I am having issues building an income statement using the Matrix table. I have provided sample data below and an end-goal picture. I am trying to achieve the below:
1) TOTAL REVENUE as a sepereate line, then TOTAL DIRECT COSTS as another line (the sum of wage, materials, equipment).
2) CONTRIBUTION MARGIN, CM %, etc on seperate total lines.
3) I would like to sort Wages first, Materials Second, Equipment third.
Trying to achieve this look:
Sample data:
| Date | Amount | OBJ Account |
| Jan-20 | 1804.68 | Revenue |
| Jan-20 | 243.3 | Revenue |
| Jan-20 | -1149.36 | Revenue |
| Jan-20 | 0 | Revenue |
| Jan-20 | -1604.1 | Revenue |
| Jan-20 | -264.98 | Revenue |
| Jan-20 | -1804.68 | Revenue |
| Jan-20 | 1149.36 | Revenue |
| Jan-20 | 0 | Revenue |
| Jan-20 | 1.3 | Revenue |
| Feb-20 | 160.8 | Revenue |
| Feb-20 | 160.8 | Revenue |
| Feb-20 | 160.8 | Revenue |
| Feb-20 | 44160.8 | Revenue |
| Feb-20 | 160.8 | Revenue |
| Feb-20 | 160.8 | Revenue |
| Feb-20 | 200 | Revenue |
| Feb-20 | 200 | Revenue |
| Feb-20 | 200 | Revenue |
| Mar-20 | 132.5 | Revenue |
| Mar-20 | 132.5 | Revenue |
| Mar-20 | 170 | Revenue |
| Mar-20 | 170 | Revenue |
| Mar-20 | 444436 | Revenue |
| Mar-20 | 200 | Revenue |
| Mar-20 | 200 | Revenue |
| Mar-20 | 200 | Revenue |
| Mar-20 | 70 | Revenue |
| Mar-20 | 70 | Revenue |
| Mar-20 | 60 | Revenue |
| Jan-20 | 44475 | Wages |
| Jan-20 | 63670 | Wages |
| Jan-20 | 14803 | Wages |
| Feb-20 | 1154 | Wages |
| Feb-20 | 2133 | Wages |
| Feb-20 | 26248 | Wages |
| Mar-20 | 945 | Wages |
| Mar-20 | 2000 | Wages |
| Mar-20 | 945 | Wages |
| Jan-20 | 41 | Materials |
| Jan-20 | 2879 | Materials |
| Jan-20 | 3599 | Materials |
| Feb-20 | 5687 | Materials |
| Mar-20 | 2807 | Materials |
| Mar-20 | 2448 | Materials |
| Jan-20 | 642 | Equipment |
| Jan-20 | 45.47 | Equipment |
| Feb-20 | 94.06 | Equipment |
| Mar-20 | 219.08 | Equipment |
| Mar-20 | 92.01 | Equipment |
3 Replies
txpower - You will need to implement a Sort By column using an if then else statement in Power Query and then sort your column by this sorting column.
txpower , You need to create sort column and then use it. example
A new column in the table
Switch( True(),
[OBJ Account] ="Wages" ,1,
[OBJ Account] ="Material" ,2,
//// Keep on adding
)
Mark it as the sort column
Refer steps :https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column
Hi, I am not following. Do you have an example work you can attach?
