Forum Discussion

Anon2020's avatar
Anon2020
Helper I
1 year ago
Solved

Matrix Showing Difference Between Two Years

I have a matrix, shown below, that I want to show the difference between the two selected years. I am unsure of the formula to calculate the difference, but when I add it to the values of the matrix,...
Need Help
Tutorial Requests
  • v-hashadapu's avatar
    v-hashadapu
    11 months ago

    Hi Anon2020 , Thank you for reaching out to the Microsoft Fabric Community Forum.

     

    I reproduced the scenario on my end using sample data and it worked successfully. To help you better understand the implementation, I’ve attached the .pbix file for your reference. Please take a look at it and let me know your observations.

     

    Thank you for being part of the Microsoft Fabric Community!

Anon2020's avatar
Anon2020
Helper I
1 year ago

The other thing is that I do not want two Diff Year Columns. Only one, on the right side of the matrix

GeraldGEmerick's avatar
GeraldGEmerick
Super User
1 year ago

Anon2020 Ah, I believe it should be this instead:

Measure = 
  VAR _maxFY = CALCULATE( MAX( Opportunity[Opp_FY_c] ), ALLSELECTED( 'Opportunity' ) )
  VAR _minFY = CALCULATE( MIN( Opportunity[Opp_FY_c] ), ALLSELECTED( 'Opportunity' ) )
  VAR _sumMaxFY = CALCULATE( SUM( 'Opportunity Product'[TotalCost] ), Opportunity[Opp_FY__c] = _maxFY )
  VAR _sumMinFY = CALCULATE( SUM( 'Opportunity Product'[TotalCost] ), Opportunity[Opp_FY__c] = _minFY )
  VAR _return = _sumMinFY - _sumMaxFY
RETURN
  _return

If you would prefer a single year column then it you would likely benefit from making this a Table visual rather than a Matrix visual.