Segmentar por "n" columnas para obtener datos por cada fila

[cymeista]

Saludos Comunidad.

Me he estado complicando demsaiado con una segmentacion de datos de una tabla en especifico que debo manejar.  He visto varias soluciones en el foro, y otros medios, mas ninguna me ha podido ayudar a obtener el resultado.

Tengo una tabla ordenada de la siguiente manera:

Proyecto	Region	Pago 1	Pago 2	Pago 3	Pago 4	Pago 5
Jardin 1	Magallanes	5.000.000	2.000.000	3.598.333	2.222.222	5.236.325
Jardin 2	Coquimbo	1.000.000	5.000.000	7.512.332	1.569.335
Jardin 3	Atacama	5.900.080	4.000.000	1.298.333

He estado tratando de elaborar una tarjeta en donde, mediante dos segmentadores de datos (uno con el "Proyecto" y otro con el "Pago x") esta me muestre el monto de pago. 

Por ejemplo, que en el segmentador de "Proyecto", seleccione Jardin 2, y que tenga otro segmentador de "Pagos" donde pueda ir navegando por sus pagos.

El segmentador 1 no es problema, pero ya al tratar de hacer el de pagos, se me complica, al tener la informacion de manera horizontal en la tabla.

Si alguien me puede ayudar con alguna idea para poder elaborar lo que comento, le agradeceria una enormidad.

Gracias y saludos.

[Anonymous · Accepted solution]

Hi  @cymeista  ,

Here are the steps you can follow：

1. Enter Power query through Transform data, select Transform – select [Pago 1], [Pago 2], [Pago 3], [Pago 4], [Pago 5] – Unpivot Columns.

Result：

2. Create calculated column.

Measure =
var _selectproyect=SELECTEDVALUE('Table'[Proyecto])
var _selectpago=SELECTEDVALUE('Table'[Attribute])
return
CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Proyecto]=_selectproyect&&'Table'[Attribute]=_selectpago))

3. Put mesaure into the card, put [Proyecto] and [Attribute] into the slicer

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Portrek]

Para lo que está diciendo, simplemente seleccione y coloque una apariencia de matriz y agregue 'pagos' en el campo de la fila y 'proyectos' en el campo de la columna.

De esta forma, los pagos serán verticales.

[Syndicate_Admin]

Thank you for your response Portrek.

Unfortunately, when I take the step you review, I have an array of this style left.

What I am looking for is to draw up a card where, through two data slicers (one with the"Project"and one with the"Payment x") the card shows me the payment amount associated with that project (where I can select different payments payment1, payment 2, etc...).

The original matrix is of this style:

I'm trying to: have a segmenter where I can select the project name, and another segmenter where I can select the EP Amount (anyone seen in the headers), and the card tells me the number of that payment for that project.

For example, Select CALICANTO, and AmountEP5, and the card shows me 45,346,286.-

Thank you and greetings.

[Portrek]

You need to put your table inside power query in format tabular.

for make that, try to find the opções that a put in image below.

link below for help you like exemplo.

https://we.tl/t-ivTmoghfM2

Best regards.

[Syndicate_Admin]

Thanks a lot. That's something I'm looking for.

I've only got one question left. You, as far as I can see, change the columns to rows.

What if I have more data in other columns in the table?

As an example, I can show you that the particular table has other columns.

Is it more advisable to build an additional table with this information (the one in the solution that you tell me), and leave the original table intact? and link both at the end?

Thank you very much for your attention.

[Portrek]

Hi, You're welcome.

No matter the number of columns in the table, you can work them within the power bi / power query and prepare them in the way that will suit you.

Working with it within the power bi, the original file will remain intact and you will be able to keep the updates the way you do today

You need to choose the best way for you, taking into account the update and your internal processes.

Best Regards.