I am trying to create an income satatement in Power BI but without getting the desried results... I have the following variables:
- Name (in table ChartofAccounts)
- No (in table ChartofAccounts)
- Year (in table dCalendar)
- Amounts (in table GLEntries)
1. I would like to see both No and Name in but currently I can only see one of them. I also want this to be sorted on No.
2. Is it possible to create sums, i. e. all accounts in the range 3000-3999 should be summarized, and show the sums as they come in the income statement? See image 2.
The account names are in Swedish, hope that's OK.
You could create a new column in the Power Query Editor in which you could add the name and No.
You could also put in the Account Name into the Rows, and then expland all of the items. After which you could go into the Propertes (Formatting Painter) and configure the Sub Totals on the level that you require.
In the header of the Matrixyou have arrows on the left and on the right side. The ones in the left drill up and down the rows you placed on your matrix the one on the right if you click on it (making it bvlakc) and the click on the last arrow in the left (double edge arrow) you will make the drill down to the bottom of the level and having it showing n.o and name.
Be aware that you also probably need to go to formatting options, row headers and then turn off stepped layout, and the subtotals also off.
To create the totals by the upper level you need to create a new column to add that level to the calculation.
Thank you both. My report looks lite this now. However, I didn't quite get how to create sums for different ranges. I actually already have this in my chart of accounts, can I use the Begin-total and End-total in some way to make it easier?
What kind of sums do you want to calculate and what logic do you use? Please post sample data of your table here that we can copy and paste following the guide in this thread: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 .
Also please post expected result based on your sample data.