Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Issue with Sum

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
 
 
 
 
 
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.