Measure to Substract values from differents rows and columns in a table

[AMDU]

Hello to everyone, i have a problem, i need to substract/sum 2 values from different rows and columns.

I have this inventory table

I need a measure because my data is very big. Also I have an index column but this is a summary filtered table table of all A and B items movements.

Thanks

[Ritaf1983 · Accepted solution]

Hi @AMDU 
You can use the offset function please refer to the tutorial:

https://www.youtube.com/watch?v=C3QY0DWts8w&t=6s

Also, you can download my PBIX with the example:

https://github.com/MadeiraData/MadeiraToolbox/blob/master/Power%20bi/Offset%20for%20difference%20bet...

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

[Ritaf1983 · Accepted solution]

Hi @AMDU 

If a calculated column is not an option take a look at the linked discussion :

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculate-Difference-Between-Rows-Ro...

in your scenario, you can modify it to the index column 

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

[Ritaf1983 · Accepted solution]

@AMDU  if the relationship is one (products) to many (fact), related should work as a calculated column.

[Ritaf1983 · Accepted solution]

Hi @AMDU 
You can use the offset function please refer to the tutorial:

https://www.youtube.com/watch?v=C3QY0DWts8w&t=6s

Also, you can download my PBIX with the example:

https://github.com/MadeiraData/MadeiraToolbox/blob/master/Power%20bi/Offset%20for%20difference%20bet...

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

[AMDU]

Thank you very much for answering Rita, i just checked the example and the problem i have is that i only want to calculate the substraction for A and B products and i have more and when i filter them the position change, lets say from "A and B" to "F and Z", so i need to have a fixed reference between these 2. But this offset function will help me a lot for other files, thank you again!

[Ritaf1983 · Accepted solution]

Hi @AMDU 

If a calculated column is not an option take a look at the linked discussion :

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculate-Difference-Between-Rows-Ro...

in your scenario, you can modify it to the index column 

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

[AMDU]

Im using a measure cause the values change when filtering dates, and other varibales like warehouse, category, and also the date is very big. When trying to use the index in the formules i have this question:

When i filter out index i get the summary of the products  ,but if i want to use the index in formula i have to use the PRODUCT TABLE index right? , how can i do this? i tried to use "related" but i got errors.

[Ritaf1983 · Accepted solution]

@AMDU  if the relationship is one (products) to many (fact), related should work as a calculated column.

[AMDU]

Thanks, could i do this using a measure? Im trying the lookupvalue but it seems the formula doesnt look up for text 

I want to look up the index from product table, i have created a second index in the right side after the products so i can use the look up.

[Ritaf1983 · Accepted solution]

I don't think so...

If your data so large , it seems you need to do some preperations from the source...