Issue with Sum

[Anonymous]

Hi All,

I am new to power bi and trying to achieve the below

create multiple columns based on conditions using if statement

Example: check UN = if( Test[Activity] = "UN" , 1, 0)

create multiple columns based on above columns

Example: 

UN value = IF(CALCULATE(SUM(Test[Value]),Test[check UN] = 1,ALLEXCEPT(Test,Test[Name],Test[Activity])),Test[Value],0)

Now I want to sum all the custom value columns created and pick only distinct values while adding them for each row

Tried Sumx value is not coming correctly.

Can some please help me on this.

Attached sample file

Thank you

[Anonymous · Accepted solution]

Hi @Anonymous 

Your judgment condition in IF dax is not clear. Calculate is a calculated value, not a judgment condition. If you run the formula you provide, there is an issue.

What are your judgment conditions? If it is satisfied, what is the value returned? If it is not satisfied, what is the return value? Can you clarify your specific needs ? Or provide your sample ?

I changed your formula, then it can run normally.

UN value =

var _sum=CALCULATE(SUM(Test[Value]),FILTER(ALLEXCEPT(Test,Test[Name],Test[Activity]),Test[check UN]=1))

return IF(Test[check UN]=1,_sum,0)

Best Regards

Community Support Team _ Ailsa Tao

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

[partymia]

I think if you go to transformation and sum the columns you need from Power Query:

-> create custom column, then sum all the once you want aggregated and when you go back to the visualization you will have the newly added columns which should calculate what you want as far as i understood. Hope this helps. 

[parry2k]

@Anonymous really not sure what you are looking for. paste sample pbix file or data with the expected output. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Can some one please let me know how can I attach same pbix file for reference
I have created a sample file with mock data to attach here but unable to attach the file