How to make the total amount display correctly ?

[Anonymous]

Hi All

My AMT_OH field expression :-

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

[v-yingjl · Accepted solution]

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]

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

[v-yingjl]

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.

[Anonymous]

Hi Ying

Thank you for your detail expliantion .

For below expressoin "RE" , i search high and low unable to fine RE.

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

[v-yingjl]

Hi @Anonymous ,

Whether using Addcolumns() or Summarize() to create a calculated table, you can find it, it stores as a variable column and could be any name like "AAA","BBB","CCC" etc.

Back to the initial measure, summarize() is better in this case because addcolumns() will calculate the wrong total value comparing with the context of the source table although both of these functions can return the same value in calculated table.

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

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.

[amitchandak]

@Anonymous , They are from same table try like

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