Forum Discussion

mp390988's avatar
mp390988
Icon for Post Partisan rankPost Partisan
1 year ago
Solved

Total column when calculating the difference between 2 matrices

Hi,

 

I have two matices, one showing values for current year and the other showing values for the previous year. Then I need to calculate the difference between the two matrices. So far, I got this but I don't have a column for totals in the matrix that is disaplaying the  differences.

 

I created a measure as per follows for calculating the differences:

RevDiff = 

VAR CurrentYearRev = CALCULATE(
    SUM(TradesPlus[GBPRevenue]),
    'Calendar'[RelativeYear] = "Year"
)
VAR PrevYearRev = CALCULATE(
    SUM(TradesPlus[GBPRevenue]),
    'Calendar'[RelativeYear] = "Year-1"
)
RETURN CurrentYearRev - PrevYearRev


Does anyone know how I can get totals displaying as well?
To be honest, the totals will be the sum of the values across the row.



Need Help
  • mp390988's avatar
    mp390988
    1 year ago

    Hi MFelix ,

     

    As I was mocking up the data and creating it in a seperate file with masked data, somehow it bizarrely started working in that new file ie. showing totals. So I no longer require the help but cheers though.

     




5 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    1 year ago

    Hi mp390988 

     

    Try the following code:

     

    RevDiff = 

VAR CurrentYearRev = CALCULATE(
    SUM(TradesPlus[GBPRevenue]),
    'Calendar'[RelativeYear] = "Year"
)
VAR PrevYearRev = CALCULATE(
    SUM(TradesPlus[GBPRevenue]),
    'Calendar'[RelativeYear] = "Year-1"
)
RETURN
SUMX( SUMMARIZE(TradesPlus, TradesPlus[Group],"VS", CurrentYearRev - PrevYearRev ),
    [VS]  )


    Be aware that this may need some adjustments did it by head, not in PBI currently.

    • mp390988's avatar
      mp390988
      Icon for Post Partisan rankPost Partisan
      1 year ago

      Hi MFelix ,

       

      Unfortunately, that doesn't give me a totals column.

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        1 year ago

        Hi mp390988 ,

         

        Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

        If the information is sensitive please share it trough private message.

  • v-venuppu's avatar
    v-venuppu
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi mp390988 ,

    Thank you for confirming that your issue got resolved. Please accept your response as solution so that if others facing similar issue can get benefitted.

     

    If you have any other issues, please raise a new thread in the community we will be happy to address ASAP.