Forum Discussion

DonRitchie's avatar
DonRitchie
Frequent Visitor
4 years ago
Solved

Calculated Column to aggregate two aggregate measures along row level of detail in a matrix

Hi,

 

This is probably something really simple that I am brainfarting on because it is Monday, but how do I sum the sums of two measures in a calculated column that aligns with row level detail like a measure would?

 

This is the measure that works: 

 

Total Pay = sum(CSV[UnattachedSettlement Pay]) + sum (DATASET[AttachedSettlementPay])

 

 Producing this result:

RowsRowsValuesValuesValues
EmployeeWeek EndingAttachedSettlementPayUnattachedSettlementPayTotal Pay
Adam6/18/22400150550
 7/2/2205050
Bob6/4/228003001100
 6/11/22150001500
 6/18/221300501350
Chuck6/25/2218001501950
 7/2/22150001500

 

I want a Calculated Column that I can put in the Rows fields that produces this result:

RowsRowsRowsValuesValues
EmployeeWeek EndingTotalPayColumnAttachedSettlementPayUnattachedSettlementPay
Adam6/18/22550400150
 7/2/2250050
Bob6/4/221100800300
 6/11/22150015000
 6/18/221350130050
Chuck6/25/2219501800150
 7/2/22150015000

 

This does not work, creating the total pay for each driver in the entire database on every row:

TotalPayColumn = CALCULATE(sum(CSV[UnattachedSettlementPay]) + sum(Dataset[AttachedSettlementPay]))

 

This gives me an error, Cannot convert value 'Adam' of type Text to type True/False.:

TotalPayColumn = CALCULATE(sum(CSV[UnattachedSettlementPay]) + sum(Dataset[AttachedSettlementPay]),DriverCardinal[Driver])

 

This does not work, creating the total pay for each driver for the displayed weeks on every row (ie, both rows for Adam are 600):

TotalPayColumn = CALCULATE(sum(CSV[UnattachedSettlementPay]) + sum(Dataset[AttachedSettlementPay]),WeekCardinal[Week Ending])

 

I am sure it is some of those filter functions inside of Calculate, but I have been banging my head on this all morning.  Any help would be appreciated.  Thanks!

  • v-jianboli-msft's avatar
    v-jianboli-msft
    4 years ago

    Hi DonRitchie 

     

    Based on your description, I created a simple sample and relationship :

     

     

     

    Here is the DAX of the calculate column:

     

    TotalPayColumn = CALCULATE(SUM('CSV'[UnattachedSettlementPay]),FILTER('CSV',[Employee]=EARLIER('DATASET'[Employee])&&[Week Ending]=EARLIER('DATASET'[Week Ending]))) + [AttachedSettlementPay]

     

    Then You can put the column in Value field to achieve your purpose.

    Final output:

     

    If the result is not what you want, please give more details.

    Refer to:

    How to provide sample data in the Power BI Forum

    How to Get Your Question Answered Quickly

     

    Best Regards,

    Jianbo Li

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

2 Replies