Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JessicaGonzalez
Frequent Visitor

Wrong Totals in a conditional measure

I have a conditional measure that is showing the wrong subtotals and totals in a matrix visualization.

 

The measure works like this:

If a worker is a contractor then I have to add all the hours they logged in Schedule. If the worker is not a contractor then I have to take the Defined Month Hours from the Calendar table. See example formula below:

 

Month Hours =IF(MAX(WorkerData[Type])="Non Contractor", SUM('Calendar'[Defined Month Hours]),CALCULATE(SUM(Schedule[HOURS]))

 

If I check each worker everything works correctly, but when checking the totals or subtotals, only Non Contractors are being added.

See example:

BossWorkerMonth Hours
Boss Subtotal 100
 Worker 1 (Non contractor)50
 Worker 2 (Non contractor)50
 Worker 3 (Contractor)5
Total 100

 

I would appreciate any help with the issue was I have been finding solutions but not for this specific type of scenario.

 

Thanks.

1 ACCEPTED SOLUTION
ppm1
Solution Sage
Solution Sage

Please see this video for how to solve this. You likely need to use something like this (replacing T1 with your actual table name.

 

NewMeasure = SUMX(Summarize(T1, T1[Boss], T1[Worker]), [YourMeasure])

 

(2) Power BI - Tales from the front #01 - Getting the Right Total - YouTube

 

Pat

Microsoft Employee

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

Does this measure work?

Measure = SUMX(VALUES(WorkerData[Type]),[Month hours])


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
ppm1
Solution Sage
Solution Sage

Please see this video for how to solve this. You likely need to use something like this (replacing T1 with your actual table name.

 

NewMeasure = SUMX(Summarize(T1, T1[Boss], T1[Worker]), [YourMeasure])

 

(2) Power BI - Tales from the front #01 - Getting the Right Total - YouTube

 

Pat

Microsoft Employee

I am afraid that the boss and the worker are at different tables.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.