topic Re: Calculating the net total (neg an positive figures) in DAX Commands and Tips

Calculating the net total (neg an positive figures)

logistics_power — Thu, 01 Aug 2024 04:17:03 GMT

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.

Re: Calculating the net total (neg an positive figures)

Dangar332 — Thu, 01 Aug 2024 06:22:21 GMT

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.

Re: Calculating the net total (neg an positive figures)

logistics_power — Thu, 01 Aug 2024 07:51:17 GMT

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"))

Re: Calculating the net total (neg an positive figures)

Dangar332 — Thu, 01 Aug 2024 08:30:21 GMT

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.