topic Re: DAX Error SUMX in DAX Commands and Tips

DAX Error SUMX

Anonymous — Sun, 07 Jul 2024 15:33:49 GMT

Hi,

I'm working on a DAX and not getting the expected results. It seems that at a line item level it is calculating correctly however, at the total level it is not summing but performing the calculation at total level. which means it is ignoring the If function. Here is my DAX:

SUMX('Calendar',IF(NOT(ISBLANK([Wages on Flat Units])), CALCULATE(SUM('WagesHomeCC and Fringe Line Items'[Amount]),Account_Map[Lvl 2] = "Total PR Tax"), 0))

Results:

Power BI is including the $67 at the total level even though there are no wages. Does anyone know how to solve this in Power BI?

Cost Ctr	Calendar	Wages on Flat Units	PR Tax
1	2024-01	5,276	441
1	2024-02	15,288	1,305
1	2024-03	5,276	581
1	2024-04	5,426	663
1	2024-05
1	2024-06		67
1	2024-07
1	2024-08
	Total (Expected)	31,265	2,990
	Total (Power BI)	31,265	3,056

Re: DAX Error SUMX

foodd — Sun, 07 Jul 2024 15:35:15 GMT

Hello Anonymous, and thank you for sharing a question with the Community. Please remember to adhere to the decorum of the Community Forum when asking a question.

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then share a file’s URL.

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523/highlight/true#M607150

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

Re: DAX Error SUMX

Jihwan_Kim — Sun, 07 Jul 2024 20:00:53 GMT

Hi,

I am not sure how your semantic model looks like, but please try something like below whether it suits your inquiry.

SUMX ( VALUES ( 'Calendar'[Calendar] ), IF ( NOT ( ISBLANK ( [Wages on Flat Units] ) ), CALCULATE ( SUM ( 'WagesHomeCC and Fringe Line Items'[Amount] ), Account_Map[Lvl 2] = "Total PR Tax" ), 0 ) )

Re: DAX Error SUMX

Anonymous — Fri, 12 Jul 2024 07:43:34 GMT

Hi,Anonymous I am glad to help you.
Hello,Jihwan_Kim ,foodd .thanks for your concern about this issue.

May I ask if your problem is solved, and if so, you can mark the suggestions you found helpful as solutions or share the solutions you used, as this will help more users with similar problems.
If your problem is not solved, you can share a .pbix file via OneDrive that does not contain sensitive data, which will help solve your problem.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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

Re: DAX Error SUMX

Anonymous — Tue, 16 Jul 2024 12:23:31 GMT

Hi,

I was able to find the solution. I was using SUMX on the connected tables but I needed to use it with a summarize on the table where the line item data is housed. He is what my code ended up looking like.

SUMX(SUMMARIZE(

'WagesHomeCC and Fringe Line Items','WagesHomeCC and Fringe Line Items'[Date], 'WagesHomeCC and Fringe Line Items'[Cost Ctr]),

IF(

NOT(ISBLANK([Wages on Flat Units])),

CALCULATE(

SUM('WagesHomeCC and Fringe Line Items'[Amount]),

Account_Map[Lvl 2] = "Total PR Tax"

)