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       C       CAN

$4       C       IND

$6       B       IND

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.

10 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Expected output is not clear

    example

    calculate(sum(Table[X]), filter(Table, Table[Y] = "A" && Table[Z] = "IND" ))

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Sorry, Let me share the expected output clearly and the current table structure

       

      The table "ABC" 

      Column AColumn BYTD

      GL Description

      		Group
        

      $40

      		ConversionCA
        $30CosourcingPD
        $80FeesCA
        $50ConversionIK
        $10Cosourcing

      MA

        $20Conversion

      CA

       

      Output should be as below only for Group "CA"

      GL DescriptionYTD
      Conversion$60
      Cosourcing$0
      Fees$80

       

       

       

      • ryan_mayu's avatar
        ryan_mayu
        Super User
        4 years ago

        Anonymous 

        I missed this reply.

        Measure 2 = CALCULATE(sum(ABC[YTD]),FILTER(ABC,ABC[Group]="CA"))+0

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    4 years ago

    Anonymous 

    what's the expected output based on your sample data?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi,

       

      The expected output is:

      Sum of column X, groupby rows in column Y and Z

       

      Thank you!

      • ryan_mayu's avatar
        ryan_mayu
        Super User
        4 years ago

        Anonymous 

        is the sample data or expected output in your first post? It does not like the Y and Z can be grouped.

        maybe you can try

        calculate(sum(x), allexcept(table, y,z))

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello,

    I am trying to calculate sum based on columns from two different tables (Actuals & MYR). I am getting the correct output for YTDActuals. But, for "YTD MYR" its not. I used the below formula for YTD Actuals:

    YTD Actuals = CALCULATE(SUMX(Actuals,Actuals[Doc Amt (USD)])). But, when I am using similar formula with reference to table "MYR" and specific column in the table, its giving total sum (same for all rows) instead of giving for each row category. Any help is greately appreciated.
    Note: I mapped the tables Actuals & MYR based on common columns.

     

     

  • 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.