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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
PineLeaf
Frequent Visitor

Measure which can retrieve values from a different column in the matrix

I'm have matrix with 2 calculated columns. Diff is easy

Diff = CALCULATE( SUM(StrategyPositions[Report.Target])-Sum(StrategyPositions[Report.Weight]))

 

What I'm finding hard to do is the Diff to Master Model A Column - I've tried:

DiffToMasterModel = SUM(StrategyPositions[Report.Weight]) - SUMX(FILTER('StrategyPositions', 'StrategyPositions'[ExplorePortfolio.PortfolioCode] = "MasterModel"), 'StrategyPositions'[Report.Weight])
What happens is for Model A thediff col is zero - good, but for Model B it is blanks (rather than what I want below).

 

I know why its not working - because the measure when running for Model B cannot see Model A's rows as they have already been filtered out. I tried an ALL FILTER(ALL('StrategyPositions')) which clears the filter but then I lose the filter context of expand/collapse the matrix levels - ie it just repeaedly gives me the same number - the sum total of A

Visually to do the diff to master all I need to do is pluck the Model A weight column at each row, but how do implement this as a measure?

 

PineLeaf_1-1680148441217.png

 

Data looks something like:

PortfolioCode, Level1, Level2, Level3, Target, Weight

 

And the Matrix  Grouping looks something like the below

 

PineLeaf_2-1680149883254.png

 

 

1 REPLY 1
lbendlin
Super User
Super User

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors