Forum Discussion
Womb
Good afternoon.
This is my first question, and I'm done here because I've been trying to recreate a table/matrix format for several days and I haven't been able to. My knowledge of the tool is basic and maybe my doubt is very silly (better, it is easy to solve it this way), but I still hope that they can help me.
I'm asked to recreate the following table (ppt image):
It's an example of what I want. Indicators and values are made up, don't take them into account.
To recreate this, I have the following table in excel:
The main thing here is that I have a column with the indicators to use in the table (either directly or those necessary to create new indicators from the ones I have, using measures I imagine), and the columns with the dates where I have values for each indicator.
I've been trying all this week to recreate the board, but there has been no way. What I really want is to be able to put as a column the indicators I want and in the order I want, and as the header of the table (apart from "indicators") the different dates that they ask me (year-end, quarter, previous year, although this one can't be done because I don't have values from the previous year). My problem has been that I am able to use the column of indicators as a column but it is difficult for me to put them in the order I would like, although I understand that it should be done with an auxiliary column as an index, and mainly, the values of each row, since when I included the values of "31/01/2020" the whole table was completed.
I hope you can help me, and I can explain my problem in detail.
Thanks a lot.
2 Replies
Syndicate_Admin You need to use a Sort By column. Other than that, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.This would be my table, I changed the indicators because they were easier that way.
Year Enterprise Indicator 31/01/2020 28/02/2020 31/03/2020 30/04/2020 31/05/2020 30/06/2020 31/07/2020 31/08/2020 30/09/2020 31/10/2020 30/11/2020 31/12/2020 Index 2022 Sony Sales 59 91 25 48 50 32 3 72 57 4 45 69 0 2022 Sony Costs of services 50 69 9 45 77 86 88 60 45 31 72 67 1 2022 Sony Other 61 61 40 52 60 1 79 33 26 17 38 1 2 2022 Sony Personal Costs 38 14 85 13 28 61 57 75 49 26 0 87 3 2022 Sony Transport costs 40 85 59 76 91 53 71 45 14 60 45 68 4 2022 Sony Gross Margin 30 32 92 48 58 14 44 82 65 41 1 64 5 2022 Sony EBITDA 7 2 49 90 28 70 100 76 88 33 79 26 6 2022 Sony EBIT 51 51 7 63 35 27 71 79 88 14 14 93 7 2022 Sony EBT 7 46 38 66 98 86 25 89 4 4 22 90 8 2022 Sony Taxes 63 100 81 25 60 86 7 31 92 66 6 8 9 2022 Sony Net Income 62 87 70 55 26 31 11 80 15 94 69 100 10 2022 Sony Provisions 61 27 3 98 74 65 24 86 48 54 62 99 11 2022 Sony Financial result 98 80 70 33 9 57 25 51 93 99 74 4 12
After some transformations, which in my opinion are convenient, the table in power BI would look like this:
And the only decent array I manage to create in Power Bi would be this:
But my intention is to have something more like this:
But if I include the indicators in the rows section, I don't get the way I'd like.
I have tried to put a copy of the table and establish a relationship between the two, so that I can have a column of indicators as I have in the original table without performing any transformation, and thus in the matrix I can have the column of indicators. I'd look something like this:
But when I add the indicator values from either table, I don't get what I'm looking for. Additionally, I attach the data that indicates to me in the visualization panel, first the transformed table, and then the original table:
I hope you can answer my question.
