calculating true sum of a measure

[aartiladdha]

I am trying to create a measure that shows actual sum total of all rows instead of just the overall.  Below is my data. Please note that all the fields are "measures" and not columns.  I want the Net Avail Supply IBP Non-Negative (Dyn) Pal measure to show 3.16 instead of 0 as shown below. This measure has a condition that

 if Total SUpply IBP (Actual Inv Only) (Pal) - Future IBP Demand Pal <0, 0 Total SUpply IBP (Actual Inv Only) (Pal) - Future IBP Demand Pal 

Tried using Sumx but it's not working for me. 

[v-zhenbw-msft · Accepted solution]

Hi @aartiladdha ,

We can use the GROUPBY function in SUMX to fix it, please change the [Measure 3] to your measure,

Measure 4 =
SUMX (
    GROUPBY ( 'Table', 'Table'[date], 'Table'[SKU], 'Table'[Plant Code] ),
    [Measure 3]
)

If it does not work, try to use a CALCULATE function like following,

Measure 4 =
SUMX (
    GROUPBY ( 'Table', 'Table'[date], 'Table'[SKU], 'Table'[Plant Code] ),
    CALCULATE ( [Measure 3] )
)

Please put the fields, which set “Do not summarize” in table visual, into the GROUPBY function and sort them as they are in table visual.

The result like this,

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

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

[amitchandak]

In sumx uses Values

Sumx(Values(Table[SKU]), <Your formula>)

or

Sumx(summarize(Table[SKU],"_1", <Your formula>),[_1])

Put your measure or calculation in <Your formula>

[aartiladdha]

This doesn't work. Also, MY tabel grain is SKU, Plant Code, DATE. How do I specify that?