Calculating the net total (neg an positive figures)

[logistics_power]

Hello Experts,

I have a matrix visual as below

I want to calculate the net variance from the freight variance column. So for supplier region US it is 500-200+300=600 and for Asia it is 1000-2000+500 = -500

Pleaase note the Total of the freight variance is a row calculated total and not the net of the column figures above it.

[Dangar332]

Hi, @logistics_power 

Try below measure.
Just rename table and column name.

Measure = 
CALCULATE(SUMX(
    FILTER(
        ALL('Table'[Region],'Table'[city],'Table'[variance]),
        'Table'[Region]=MAX('Table'[Region]) && 'Table'[city]<>"Total"
        ),
        [variance]
),KEEPFILTERS('Table'[city]="total"))

Best regards,
Dangar 

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

[logistics_power]

Thanks for your response @Dangar332 

I am getting an error "The report measure [Variance] has a syntax or semantic error.

ALL/ALLNOBLANK?ALLSELECTED functions must be from the same table.

Just FYI the fields Supplier region, Destination city, Transaction type are from different tables.

Another question in the measure Table'[variance] is a calculated measure itself so does that have to be a part of ALL?

Measure =
CALCULATE(SUMX(
FILTER(
ALL('Table'[Region],'Table'[city],'Table'[variance]),
'Table'[Region]=MAX('Table'[Region]) && 'Table'[city]<>"Total"
),
[variance]
),KEEPFILTERS('Table'[city]="total"))

[Dangar332 · Accepted solution]

Hi, @logistics_power 

All family function must be from same table that's why it give error

Try below measure

Measure = 
CALCULATE(
sumx(
filter(
 SUMMARIZE('Table'[Region],'Table'[city]),
'Table'[Region]=MAX('Table'[Region]) && 'Table'[city]<>"Total"
),[variance_measure])
),KEEPFILTERS('Table'[city]="total"))

Best regards,
Dangar 

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