Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to make the total amount display correctly ?

Hi All

 
My AMT_OH field expression :-
AMT_OH = SUM(INVC[Unit Cost])*SUM(INVC[Avail])
 

 

 

Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi Anonymous ,

    Based on your description, you can modify the measure like this:

    AMT_OH =
VAR tab =
    SUMMARIZE (
        'Table',
        'Table'[Stock Code],
        'Table'[Unit Cost],
        'Table'[Avail],
        "RE", SUM ( 'Table'[Unit Cost] ) * SUM ( 'Table'[Avail] )
    )
RETURN
    SUMX ( tab, [RE] )

    Attached a sample file in the below, hopes to help you.

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , They are from same table try like

    AMT_OH = SUMX(INVC, INVC[Unit Cost])*SUM(INVC[Avail])

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,

    Based on your description, you can modify the measure like this:

    AMT_OH =
VAR tab =
    SUMMARIZE (
        'Table',
        'Table'[Stock Code],
        'Table'[Unit Cost],
        'Table'[Avail],
        "RE", SUM ( 'Table'[Unit Cost] ) * SUM ( 'Table'[Avail] )
    )
RETURN
    SUMX ( tab, [RE] )

    Attached a sample file in the below, hopes to help you.

     

    Best Regards,
    Community Support Team _ Yingjie Li
    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
      5 years ago

      Thank you very much , i unable to understand your script. can you explain a bit ?

      • v-yingjl's avatar
        v-yingjl
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi Anonymous ,

        Sure. The variable extract the source table and add a new column for it to calculate [unit cost] * [avil] for current context, it could be more simple like this:

        a =
ADDCOLUMNS ( 'Table', "RE", [Unit Cost] * [Avail] )

        You can use the above formula to replace the variable and create a new calculated table to check which value it returns that should be the same as the initial formula.

        For SUMX(), calculate the sum value for the field in the variable table which should be look like the same as the initial table and you will get the expected output.

         

        In addition, you can refer these documents about the dax functions that I quoted:

        1. SUMX() 
        2. SUMMARIZE() 
        3. ADDCOLUMNS() 

         

        Best Regards,
        Community Support Team _ Yingjie Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.