Forum Discussion

mp390988's avatar
mp390988
Post 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
    Super 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.

      • MFelix's avatar
        MFelix
        Super 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
    Community 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.