Calculate a mesure and total in a matrix

[JosePowerbi]

Hello.

I have a table with the following data , and i want to made this matrix.

             

I have made the following steps.

1.  Matrix with a meaure.   M_Cantidad A-B

      M_Cantidad A-B = SUM(TablaDatos[Cantidad])

2. Matrix with a measure  M_cantidad B-A ,   the values are corrected, but the totals a wrong or blank

M_Cantidad B-A =

var comp =SELECTEDVALUE(TablaDatos[Comprador])

var vend =SELECTEDVALUE(TablaDatos[Vendedor])

var cantidad =CALCULATE(

[M_Cantidad A-B];

FILTER(

CALCULATETABLE(TablaDatos;

ALL(TablaDatos[Comprador]);

ALL(TablaDatos[Vendedor])

);

AND( TablaDatos[Vendedor]=comp ;TablaDatos[Comprador]=vend)

)

)

return

cantidad

3 Matrix with de two measures [M-Cantidad A-B] and [M_cantidad B-A], the next step calculate the diference

4. Matrix with a measure  M_Cantidad K0,   the values in the matrix are ok, but the totals a wrong.

M_Cantidad KO =

var Rdo= [M_Cantidad A-B]-[M_Cantidad B-A]

return

if (rdo<0;0;rdo)

how can i get the matrix with the correct totals ??

thanks in advanced

jose

[JosePowerbi · Accepted solution]

Hi.

The form of calculation is as follows.

The scenario is ...

the row  Client A , Client B ,100    is.., the client A give a quantity 100,  to client B

the row  Client B ,  Client A, 20     is  the client B give a quaity 20 to cliente A.

In this case..

The row Client A, Client B   has  100 - 20  = 80

the row  Client B, client  A  has    20 -100  = -80  the numbers <0 , is 0

thus, with the posible pairs  (Client X- Client Y )   -   (Client Y- Client X )  that you have in the datatable.

 thanks

jose

[Ashish_Mathur]

Hi,

Please explain the question in detail.  Why is the value at the intersection of Client A and Client B 80?

[JosePowerbi · Accepted solution]

Hi.

The form of calculation is as follows.

The scenario is ...

the row  Client A , Client B ,100    is.., the client A give a quantity 100,  to client B

the row  Client B ,  Client A, 20     is  the client B give a quaity 20 to cliente A.

In this case..

The row Client A, Client B   has  100 - 20  = 80

the row  Client B, client  A  has    20 -100  = -80  the numbers <0 , is 0

thus, with the posible pairs  (Client X- Client Y )   -   (Client Y- Client X )  that you have in the datatable.

 thanks

jose

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

HI @JosePowerbi ,

Please take a look at following link about how to fix total level calculation issues:

Measure Totals, The Final Word

Regards,
Xiaoxin Sheng

[JosePowerbi]

Hello Xiaoxin Sheng.

I see the link and I find it very interesting, but I have not managed to generate the matrix.

I leave a link with the pbix file to see if you can help me get it

https://mega.nz/#!iB5WlIIB!Rs8w4nr-g1rz6tZj842va2BT88aJrcNU2kBjd5f3LqI

thanks in advaced

jose