Need help with calculation, Column value - Measure

[Anonymous]

Hi all, full disclosure I'm pretty new to PowerBI and I'm running into a bit of a pickle. 

I'm currently building a dashboard in order to properly visualize a custom index that I'm calculating from CFTC reports.

My end goal is to visualize the index on a chart on a week by week basis, as my data is released on a weekly basis (don't worry I already figured out how to do a weekly heirarchy) . I'm currently trying to create a measure that will give me the numerator of a DIVIDE function.

The problem is I can't seem to subtract a measure from a column value. I want to generate a new value for this index for every week, but I can't use a calculated column because the measures are dependent on slicers. 

I currently have this :

Commercial COT Numerator = SUMX('COT V2 Reports', 'COT V2 Reports'[Commercial Net Position] - [Minimum Commercial Net Position])

 The result I get from this is Zero.  If I use the SUM function it just aggregates the entire table and throws off my data. What I want is to take the Commercial Net Position column and subtract with Minimum Commercial Net Position for every single row, thus generating a different index value for every week. 

Does anyone have any tips for this? It seems like a simple calculation and I've been stuck for the past few days. 

[TomMartens · Accepted solution]

Hey @Anonymous ,

assuming that [Minimum Commercial ...] is a measure (as you are not using a 'table' reference, this happens during the iteration of SUMX:

1. Slicers are filtering the table 'Cot V2 Reports'
2. the measure [Minimum ...] transforms the row context of the iteration into a filter context. All the columns of the current row become a filter. This filter context is used to evaluate the measure.

From the information you provided, I just can recommend these two things

• Adjust the table used inside SUMX, that the [Minimum ...] returns the result you are expecting
• use  ... - CALCULATE( [Minimum ...] , change the filter context) )

This article desribes in great detail what is going on: MDX and DAX topics: The Logic behind the Magic of DAX Cross Table Filtering (mdxdax.blogspot.com)
Hopefully, this provides some ideas on how to tackle your challenge. If not, consider to create a pbix that contains sample data but still reflects your data model. Upload the pbix to onedrive or dropbox and share the linl. If you are using Excel to create the sample data, share the xlsx as well.
Do not forget to describe the expected result based on the sample data you are using.

Regards,

Tom

[Anonymous]

Aha, that would make sense, thank you!