Forum Discussion

pyramid1's avatar
pyramid1
Frequent Visitor
3 years ago
Solved

PowerBI Matrix Table with finding difference between two column and display it

Hello expert, I need some help with Matrix.   Here is my sample data   Person Year  Salary   Tax  P1 2021  $ 300.00  $   27.00 P2 2021  $ 400.00  $   56.00 P3 2021  $ 500.0...
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi pyramid1 ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) My test data is the same as yours.

    (2)Click "transform data" to enter the power query editor, select the [Year] column and [Person] column, and click transpose. Click "Close and apply".

    (3) We can create a measure. 

    Diff = 
    var _a=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Person]=MAX('Table'[Person]) && 'Table'[Attribute]=MAX('Table'[Attribute]) && 'Table'[Year]=2022))
    var _b=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Person]=MAX('Table'[Person]) && 'Table'[Attribute]=MAX('Table'[Attribute]) && 'Table'[Year]=2021))
    var _d=FORMAT(_a-_b,"Fixed")
    var _c=IF(_a-_b < 0, "-" & "$"& _d,"$" & _d)
    return IF(SELECTEDVALUE('Table'[Year])=2021,BLANK(),_c)

    (4) Then the result is as follows.

    Due to the design of the power bi desktop, the matrix may not display exactly what you expect. You can create a shapes in the [Insert] pane and set its background color to white and no border to block the fields you don't want to display.

    Best Regards,

    Neeko Tang

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