Forum Discussion

Naveen123's avatar
Naveen123
Frequent Visitor
3 years ago
Solved

How to show difference between two values in Matrix view

Hi All,

 

I have used Matrix view to display profit and sales based on Fiscal year. I am not sure how to display the difference between 2023, 2022 sales and profit for each row. 

 

Thanks in advance!

Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Naveen123 ,

     

    If your table looks like as below, I suggest you to try to show difference in Column Subtotal part.

    M_Sales = 
VAR _THISYEAR = CALCULATE(SUM('Table'[Sales]),'Table'[Year] = 2023)
VAR _LASTYEAR = CALCULATE(SUM('Table'[Sales]),'Table'[Year] = 2022)
RETURN

IF(HASONEVALUE('Table'[Year]),CALCULATE(SUM('Table'[Sales])),_THISYEAR - _LASTYEAR)
    M_Profit = 
VAR _THISYEAR = CALCULATE(SUM('Table'[Profit]),'Table'[Year] = 2023)
VAR _LASTYEAR = CALCULATE(SUM('Table'[Profit]),'Table'[Year] = 2022)
RETURN

IF(HASONEVALUE('Table'[Year]),CALCULATE(SUM('Table'[Profit])),_THISYEAR - _LASTYEAR)

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Naveen123 , You can use diff measure

    example

    //Only year vs Year, not a level below

    This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
    Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
    diff = [This Year]-[Last Year ]
    diff % = divide([This Year]-[Last Year ],[Last Year ])

     

     

    or you can follow this approch

    Power BI Time Intelligence- Show month names instead of Current Month Vs Last Month- https://youtu.be/x0DvPn1QeO4

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Naveen123 ,

     

    If your table looks like as below, I suggest you to try to show difference in Column Subtotal part.

    M_Sales = 
VAR _THISYEAR = CALCULATE(SUM('Table'[Sales]),'Table'[Year] = 2023)
VAR _LASTYEAR = CALCULATE(SUM('Table'[Sales]),'Table'[Year] = 2022)
RETURN

IF(HASONEVALUE('Table'[Year]),CALCULATE(SUM('Table'[Sales])),_THISYEAR - _LASTYEAR)
    M_Profit = 
VAR _THISYEAR = CALCULATE(SUM('Table'[Profit]),'Table'[Year] = 2023)
VAR _LASTYEAR = CALCULATE(SUM('Table'[Profit]),'Table'[Year] = 2022)
RETURN

IF(HASONEVALUE('Table'[Year]),CALCULATE(SUM('Table'[Profit])),_THISYEAR - _LASTYEAR)

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.