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

 

PersonYear Salary  Tax 
P12021 $ 300.00 $   27.00
P22021 $ 400.00 $   56.00
P32021 $ 500.00 $   38.00
P12022 $ 370.00 $   40.00
P22022 $ 430.00 $   20.00
P32022 $ 478.00 $   20.00

 

 

Here is my desired output.

 

 Salary  Tax  
Person20212022Diff20212022Diff
P1 $ 300.00 $ 370.00 $   70.00 $   27.00 $   40.00 $   13.00
P2 $ 400.00 $ 430.00 $   30.00 $   56.00 $   20.00-$   36.00
P3 $ 500.00 $ 478.00-$   22.00 $   38.00 $   20.00-$   18.00

 

Output in png format (I tried to paste output in table format above but seems post is auto formatted): 

I tried to put this data in PowerBI matrix but seems unable to calculate measure to find difference between year and display it next to year. 

 

Any help would be appreciate.

Need Help
  • 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. 

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.