Forum Discussion

homboy27's avatar
homboy27
Icon for Helper III rankHelper III
7 years ago
Solved

Difference column in a matrix

Client Hours Revenue X 123 20000 Y 100 10000   23 10000       I created the above table in PowerBI. How do i create the 3rd column that subtracts client x from y?
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
7 years ago

Hi,

Assuming Hours and Revenue are measures, try this measure

=[Revenue]-CALCULATE([Revenue],Data[Client]="Client X")

Hope this helps.

homboy27's avatar
homboy27
Icon for Helper III rankHelper III
7 years ago

That didnt work. Also hours and revenues are not measures.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    Hi,

    Share the link from where i can download your PBI file.  Also show the expected result.

    • homboy27's avatar
      homboy27
      Icon for Helper III rankHelper III
      7 years ago
      ClientHoursRevenue
      X12320000
      Y10010000
      Expected Result2310000

       

      please see above. i created a table with the first 3 rows. Expected result column is what i would like to create. It basically subtracts the value from columns 2 and 3.

    • parry2k's avatar
      parry2k
      Icon for Super User rankSuper User
      7 years ago

      homboy27 add two new measures as belowo, drop table visual with Client, Total Hours, Total Revenue and you will have your solution

       

      Total Revenue = 
IF(  ISINSCOPE( Table3[Client] ),
    SUM( Table3[Revenue] ) ,
    CALCULATE( SUM( Table3[Revenue] ), Table3[Client] = "X" ) -
    CALCULATE( SUM( Table3[Revenue] ), Table3[Client] = "Y" )
)

Total Hours = 
IF(  ISINSCOPE( Table3[Client] ),
    SUM( Table3[Hours] ) ,
    CALCULATE( SUM( Table3[Hours] ), Table3[Client] = "X" ) -
    CALCULATE( SUM( Table3[Hours] ), Table3[Client] = "Y" )
)
      • homboy27's avatar
        homboy27
        Icon for Helper III rankHelper III
        7 years ago

        That did not work either