Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX

Hi, I need to calculate sum of $ amounts in column X based on condition GroupBy in column Y, Z.   Ex:   X          Y     Z $5       A      IND $6       A       US $7       B       US $8     ...
Need Help
  • v-henryk-mstf's avatar
    v-henryk-mstf
    4 years ago

    Hi Anonymous ,

     

    According to your description, create the following virtual table.

    Out_result =
SUMMARIZE (
    ABC,
    ABC[GL Description],
    "A",
        IF (
            NOT ISBLANK (
                CALCULATE (
                    SUM ( ABC[YTD] ),
                    FILTER (
                        ALL ( ABC ),
                        MAX ( ABC[GL Description] ) = ABC[GL Description]
                            && ABC[Group] = "CA"
                    )
                )
            ),
            CALCULATE (
                SUM ( ABC[YTD] ),
                FILTER (
                    ALL ( ABC ),
                    MAX ( ABC[GL Description] ) = ABC[GL Description]
                        && ABC[Group] = "CA"
                )
            ),
            0
        )
)


    If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


    Best Regards,
    Henry


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

v-henryk-mstf's avatar
v-henryk-mstf
Community Support
4 years ago

Hi Anonymous ,

 

According to your description, create the following virtual table.

Out_result =
SUMMARIZE (
    ABC,
    ABC[GL Description],
    "A",
        IF (
            NOT ISBLANK (
                CALCULATE (
                    SUM ( ABC[YTD] ),
                    FILTER (
                        ALL ( ABC ),
                        MAX ( ABC[GL Description] ) = ABC[GL Description]
                            && ABC[Group] = "CA"
                    )
                )
            ),
            CALCULATE (
                SUM ( ABC[YTD] ),
                FILTER (
                    ALL ( ABC ),
                    MAX ( ABC[GL Description] ) = ABC[GL Description]
                        && ABC[Group] = "CA"
                )
            ),
            0
        )
)


If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


Best Regards,
Henry


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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks for your help on this. However, when use the calculation formula as above, I get an error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

    • v-henryk-mstf's avatar
      v-henryk-mstf
      Community Support
      4 years ago

      Hi Anonymous ,

       

      Is the formatting used correctly in the formula you created? During my testing, this problem was not found. Did you refer to the pbix file I provided? If there is still confusion, please provide screenshots of the relevant test results.


      Looking forward to your reply.


      Best Regards,
      Henry


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