Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
AMDU
Frequent Visitor

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

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

 

I have this inventory table

AMDU_2-1695416574680.png

 

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

 

 

1 ACCEPTED SOLUTION

I don't think so...

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

7 REPLIES 7
Ritaf1983
Super User
Super User

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

 

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

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!

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

 

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

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:

AMDU_0-1695475321794.png

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.

 

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

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

AMDU_0-1695477738796.png

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.

I don't think so...

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.