topic Re: Measure to sum negative values from another measure not displaying correctly in DAX Commands and Tips

Measure to sum negative values from another measure not displaying correctly

Deevo_ — Thu, 11 Apr 2024 04:34:48 GMT

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:

ProjectTable (Dimension)
ProjectEffortTable (Fact)
AssignedTable (Fact)
Teams (Dimension)
Role (Dimension)
Specialisation (Dimension)

These are my measures:

ProjectEffortTotal (Hrs) = SUM('ProjectEffortTable'[Projectefforthours])
AssignedEffortTotal (Hrs) = SUM('AssignedTable'[AssignedEffortHours])
Variance (Hrs) = [ProjectEffortTotal (Hrs)] - [AssignedEffortTotal (Hrs)]
Positive Measure = SUMX(filter('ProjectTable', [Variance (Hrs)] > 0), [Variance (Hrs)])
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.

Re: Measure to sum negative values from another measure not displaying correctly

Deevo_ — Fri, 12 Apr 2024 02:52:56 GMT

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/627885

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

)