Matrix Showing Difference Between Two Years

[Anon2020]

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, the result shows the difference btween the tweo sets of data. Here is an example of what I am looking for:

[v-hashadapu]

Hi @Anon2020 , Hope you're doing fine. Can you confirm if the problem is solved or still persists? Sharing your details will help others in the community.

[v-hashadapu]

Hi @Anon2020 , Hope you're doing okay! May we know if it worked for you, or are you still experiencing difficulties? Let us know — your feedback can really help others in the same situation.

[v-hashadapu · Accepted solution]

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!

[v-hashadapu]

Hi @Anon2020 , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

Also, your sample data links doesn't work, so if your issue is not solved. can you please re-send them. 

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[Anon2020]

Let me know if you have troublke accessing the link

[alish_b]

Hello @Anon2020 ,

The link does not work. Please store the pbix file in a Google drive (with public permissions) or a Dropbox (again public) or a public repo in Github whichever you prefer. Please make sure that you remove any sensitive data beforehand. 
Or, you could simply add sample data in a table format (that can be copied so not an image) and drop the measure definitions (if any) or any particular steps to recreate your visual. 

[Anon2020]

https://app.powerbi.com/links/MibnxtDhHA?ctid=df807e4b-2999-4706-af81-be54e26b1e03&pbi_source=linkSh...

[Anon2020]

https://app.powerbi.com/links/MibnxtDhHA?ctid=df807e4b-2999-4706-af81-be54e26b1e03&pbi_source=linkSh...

[Ashish_Mathur]

That is not a downoadable file link.  That link takes me to a sign-in page.

[Anon2020]

https://app.powerbi.com/reportEmbed?reportId=1dddffed-da4a-47eb-851d-8dcf974211cc

[Anon2020]

do you have an email I can send to? It is in my org's salesforce environment

[Anon2020]

this is as close as I can get, but still not showing difference, and still showing values between fiscal years instead of in the last column

[GeraldGEmerick]

@Anon2020 You would need to get the Maximum and Minimum values for the Opp_FY_c column. Then you would need to have 2 CALCULATE statements where you calculate the SUM for the TotalPrice column, once for the Maximum value and once for the Minimum value. Modify your filter clause within each CALCULATE statemetn. Then you simply need to subtract one from the other.

[Anon2020]

would be be so kind to show me?

[GeraldGEmerick]

@Anon2020 I can certainly endeavor to do so. 

Measure = 
  VAR _maxFY = MAX( Opportunity[Opp_FY_c] )
  VAR _minFY = MIN( Opportunity[Opp_FY_c] )
  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

Hopefully this gets you the answer you are looking for or at least gets you close enough.

[Anon2020]

I keep thrwoing zeros, and do not know why. Can I send you the pbix file?

[Anon2020]

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

[GeraldGEmerick]

@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.