topic Re: Matrix row total not accurately calculating in DAX Commands and Tips

Matrix row total not accurately calculating

A_Scott — Thu, 11 Jul 2024 15:35:22 GMT

I have a matrix viual that list rows for Division/Zone/Location # and provides the percent of inspections completed.

*Current matrix visual:

*Current calculation for the column in question:

% Baler Complete Average =

IF(

[Baler Count] = 0,

"N/A",

CALCULATE(

MIN(1, [Completed Baler Inspections] / [Min. Baler Insp. Required])

)) +0

*Calculation for [Baler Count]:

Baler Count = CALCULATE(

COUNTROWS('Equip IDs'),

FILTER('Equip IDs','Equip IDs'[Equipment Product Type Descr]="BALERS AND COMPACTORS")) +0

*Calculation for [Completed Baler Inspections] :

Completed Baler Inspections = CALCULATE(

COUNTROWS('BALER_INSPECTION_LOG'))

*Calculation for [Min. Blaer Insp. Required]:

Min. Baler Insp. Required = [Baler Count]*7

*Tables 'Equip IDs' and 'BALER_INSPECTION_LOG' are linked many to many with 'Equip IDs' leading the single cross filter direction.

How do I make the % Baler Complete Average Total show the average of the rows and not a direct calculation of the sum of the other columns?

Re: Matrix row total not accurately calculating

Greg_Deckler — Thu, 11 Jul 2024 15:53:31 GMT

A_Scott First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

Re: Matrix row total not accurately calculating

A_Scott — Thu, 11 Jul 2024 16:05:24 GMT

I've read both of those articles and still not grasping how to make it apply to my senario...

Re: Matrix row total not accurately calculating

Greg_Deckler — Thu, 11 Jul 2024 16:09:16 GMT

A_Scott OK, can you post sample data.

Re: Matrix row total not accurately calculating

A_Scott — Thu, 11 Jul 2024 16:41:42 GMT

BALER_INSPECTION_LOG sample:

Inspection #	Division	Zone	Location #	Unique Baler ID	Log Date	Log Time
458	011	01	210	011-00210-3251259	7/6/2024	6:13:42 AM
459	011	01	210	011-00210-3259534	7/6/2024	6:14:16 AM
460	011	01	469	011-00469-3280351	7/6/2024	6:32:22 AM
461	011	01	426	011-00426-3302077	7/6/2024	6:34:19 AM
462	011	01	210	011-00210-3251259	7/6/2024	7:32:14 AM
463	011	01	441	011-00441-3275603	7/6/2024	7:46:01 AM
464	011	01	469	011-00469-3280351	7/6/2024	8:36:08 AM
465	011	01	390	011-00390-3268426	7/6/2024	8:42:18 AM
466	011	01	390	011-00390-3268425	7/6/2024	8:47:43 AM
467	011	01	469	011-00469-4201967	7/6/2024	8:48:06 AM
468	011	01	381	011-00381-3268427	7/6/2024	9:06:06 AM
469	011	01	437	011-00437-3268362	7/6/2024	9:09:50 AM
470	011	01	467	011-00467-3264610	7/6/2024	9:42:13 AM
471	011	01	653	011-00653-3298043	7/6/2024	9:52:13 AM
472	011	01	653	011-00653-3298043	7/6/2024	9:52:23 AM
473	011	01	441	011-00441-3275603	7/6/2024	10:32:57 AM
474	011	01	486	011-00486-3270802	7/6/2024	11:18:07 AM
475	011	01	434	011-00434-3268428	7/6/2024	12:39:47 PM
476	011	01	437	011-00437-3268362	7/6/2024	12:57:02 PM

Equip IDs sample:

Division	Zone	Location #	Unique Equip ID	Equipment Product Type Descr
011	01	210	011-00210-3251259	BALERS AND COMPACTORS
011	01	324	011-00324-3268432	BALERS AND COMPACTORS
011	01	344	011-00344-3262636	BALERS AND COMPACTORS
011	01	381	011-00381-3268427	BALERS AND COMPACTORS
011	01	390	011-00390-3268425	BALERS AND COMPACTORS
011	01	390	011-00390-3268426	BALERS AND COMPACTORS
011	01	426	011-00426-3302077	BALERS AND COMPACTORS
011	01	434	011-00434-3268428	BALERS AND COMPACTORS
011	01	437	011-00437-3268362	BALERS AND COMPACTORS
011	01	441	011-00441-3275603	BALERS AND COMPACTORS
011	01	467	011-00467-3264610	BALERS AND COMPACTORS
011	01	469	011-00469-3280351	BALERS AND COMPACTORS
011	01	473	011-00473-3269449	BALERS AND COMPACTORS
011	01	486	011-00486-3270802	BALERS AND COMPACTORS
011	01	653	011-00653-3298043	BALERS AND COMPACTORS
011	01	682	011-00682-3255094	BALERS AND COMPACTORS
011	01	682	011-00682-3255121	BALERS AND COMPACTORS
011	01	687	011-00687-3259870	BALERS AND COMPACTORS

Re: Matrix row total not accurately calculating

Anonymous — Wed, 17 Jul 2024 09:08:29 GMT

Hi A_Scott ,

Thank you Greg_Deckler very much for the solution, and I've tried other ways to help you understand the problem:

You can use ISINSCOPE in DAX for contextual filtering if you want to use each row for counting and not each column.

% Baler Complete Average = IF ( SUM('Table'[Baler Count]) = 0, "N/A", IF ( ISINSCOPE('Table'[Location]), MIN(1, SUM('Table'[Completed Baler Inspections]) / SUM('Table'[Min. Baler Insp. Required])), AVERAGEX ( VALUES('Table'[Location]), MIN(1, SUM('Table'[Completed Baler Inspections]) / SUM('Table'[Min. Baler Insp. Required])) ) ) )

Except for the % Baler Complete Average, which is all columns and does not use DAX,

you can remove my SUM function depending on the conditions and it should accomplish what you need as well.

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Re: Matrix row total not accurately calculating

A_Scott — Tue, 23 Jul 2024 17:02:27 GMT

This worked perfectly. Thank you for your simple explination!