Need help fixing Dax measure

[Meghbajaj]

Hi community, I need help with following problem,

Actual Data:

Result I am getting:

Result Required:

Thank you for help.

[FreemanZ]

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]

)

[smpa01]

@Meghbajaj 

Measure = CALCULATE(SUMX(ALL(tbl[salary]),[salary]),ALL(tbl))

[Meghbajaj]

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?

[FreemanZ]

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. 

[grantsamborn]

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]

@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? 

[hummingbird]

Can you please provide more information about the DAX code you have written and your desired outcome in Power BI? This will help me better understand what you are trying to achieve and how I can assist you. @Meghbajaj, I would appreciate it if you could share more details about your project.

[Meghbajaj]

Hi @hummingbird, thanks for your response.
I am trying to get the Total (Sum) of Salary by Plan Type. Total Salary = Base + Rent + Phone with respect to the Salary Start Date. Through my Dax forumula using Sum/Sumx, I am getting Total Salary = 3750 ie. 1000+1000+1000+200+200+200+50+50+50. However, what i need is 1000+200+50. I hope it makes sense.