Forum Discussion

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

hi Meghbajaj 

try to add a column like:

Total Salary =
SUMX(
    SUMMARIZE(
        TableName,
        TableName[ID],
        TableName[Salary Start Date],
        TableName[Salary Type]
        TableName[Salary]
    ),
    TableName[Salary]
)
Meghbajaj's avatar
Meghbajaj
Frequent Visitor
3 years ago

Hi FreemanZ, thanks for your reply. Unfortunately this didn't work as a solution to my problem. Is there any other method you can suggest?

 

  • grantsamborn's avatar
    grantsamborn
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Have you considered a calculated column?

    cTotal_Salary = 
VAR _ID = TableName[ID]
VAR _Date = TableName[Date]
VAR _Result =
    SUMX(
        FILTER(
            ALL( TableName ),
            TableName[ID] = _ID
                && TableName[Date] = _Date
        ),
        TableName[Salary]
    )
RETURN
    _Result

    If you use this column in a table or matrix, you might have to adjust the aggregation for the total line.

    Let me know if this helps.

    • grantsamborn's avatar
      grantsamborn
      Icon for Solution Sage rankSolution Sage
      3 years ago

      Meghbajaj 

      I made up some fake data and that usually worked.  I'm trying to figure out why there are cases that didn't work.

      Is there any way you can come up with more sample data? 

       

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi Meghbajaj 

    there was a typo in the previous code, is it what you expect?

    Total Salary2 = 
SUMX(
    SUMMARIZE(
        TableName,
        TableName[ID],
        TableName[Salary Start Date],
        TableName[Salary Type],
        TableName[Salary]
    ),
    TableName[Salary]
)

     

    it worked like:

     

    you see there many duplicates in the calculated column. it makes more sense with a measure. The code is also good for a measure. It works like:

     

    the measure could be further optimized if more context could provided.