Forum Discussion

A_Scott's avatar
A_Scott
Icon for Helper I rankHelper I
2 years ago
Solved

Matrix row total not accurately calculating

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?
 
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

6 Replies