Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Meghbajaj
Frequent Visitor

Need help fixing Dax measure

Hi community, I need help with following problem,

Actual Data:

Meghbajaj_0-1676191714872.png

 

Result I am getting:

Meghbajaj_1-1676191749072.png

 

Result Required:

Meghbajaj_2-1676191770519.png

 

Thank you for help.

8 REPLIES 8
FreemanZ
Super User
Super User

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 

Measure = CALCULATE(SUMX(ALL(tbl[salary]),[salary]),ALL(tbl))
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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?

 

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:

FreemanZ_0-1676256228839.png

 

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:

FreemanZ_1-1676256333964.png

 

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

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.

@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
Helper II
Helper II

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.

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.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.