Measure to sum negative values from another measure not displaying correctly

[Deevo_]

Hi all,

First off, I have created 2 separate measures to SUM positive and negative values. The positive measure appears to be working correctly, but the negative measure is not.

I have 5 tables:

1. ProjectTable (Dimension)
2. ProjectEffortTable (Fact)
3. AssignedTable (Fact)
4. Teams (Dimension)
5. Role (Dimension)
6. Specialisation (Dimension)

These are my measures:

1. ProjectEffortTotal (Hrs) = SUM('ProjectEffortTable'[Projectefforthours])
2. AssignedEffortTotal (Hrs) = SUM('AssignedTable'[AssignedEffortHours])
3. Variance (Hrs) = [ProjectEffortTotal (Hrs)] - [AssignedEffortTotal (Hrs)]
4. Positive Measure = SUMX(filter('ProjectTable', [Variance (Hrs)] > 0), [Variance (Hrs)])
5. Negative Measure = SUMX(filter('ProjectTable', [Variance (Hrs)] < 0), [Variance (Hrs)])

Matrix Table Hierachry:

I have a matrix table which uses 4 fields from 4 different tables and the display hierarchy is: 'ProjectTable'[Project Code], 'Teams'[Team Name], 'Role'[Role Name], 'Specialisation'[Specialisation Name].

The result is below:

Any advice on what I am doing wrong and how to fix it would be great?

I appreciate it.

[Deevo_ · Accepted solution]

After much reading, testing and headache, I came across a forum post that worked for me. The CROSSJOIN was the determining factor here as I was using columns from multiple tables in my matrix table. I hope this helps others.

Please see link: https://community.fabric.microsoft.com/t5/Desktop/Sum-measure-total-on-only-negative-values/m-p/6278...

My new working formulas:

Negative Measure =

--This measure only returns the over assigned hours and then totals them

SUMX (

    CROSSJOIN ( VALUES('ProjectTable'[PSCode]), VALUES( 'Teams'[TeamName]), VALUES('Role'[RoleName]), VALUES(Specialisation[SpecialisationName])),

    IF ( [ProjectEffortTotal (Hrs)] - [AssignedEffortTotal (Hrs)] < 0, [ProjectEffortTotal (Hrs)] - [AssignedEffortTotal (Hrs)], BLANK () )

)

Positive Measure =

--This measure only returns the under assigned hours and then totals them

SUMX (

    CROSSJOIN ( VALUES('ProjectTable'[PSCode]), VALUES( 'Teams'[TeamName]), VALUES('Role'[RoleName]), VALUES(Specialisation[SpecialisationName])),

    IF ( [ProjectEffortTotal (Hrs)] - [AssignedEffortTotal (Hrs)] > 0, [ProjectEffortTotal (Hrs)] - [AssignedEffortTotal (Hrs)], BLANK () )

)