Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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:
These are my measures:
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.
Solved! Go to 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 () )
)
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 () )
)
User | Count |
---|---|
53 | |
46 | |
19 | |
16 | |
13 |
User | Count |
---|---|
108 | |
56 | |
29 | |
21 | |
18 |