Grand Total on Matrix Help

[MeyerRy]

I have the following measure that is mostly working. I just need to get the grand total working. See line 23 where I hard keyed "321" and then see the matrix output. I am wanting everything in the Average column to be summed for the grand total. If I leave 

IVIDE(AllProjectsAdjustedCost, AllProjectsCount) in the grand total sums the 3 project totals and divides by 3. This isn't quite right as the average column will only divide by the number of projects if there is a cost value in the rows. I am looking for help getting that average column to ignore everything else and just sum the values above it.

[MeyerRy]

Thanks for the statement. I updated my measure to reflect that statment and came up with the following measure. The Cell values, Column Totals, and Row Averages are correct. I am still having trouble summing the Row Averages to come up with the Grand Total.  The Row Averages divide by the number of estimates but only if there is a value in the row. The dataset export below shows should some are divided by two and some are just dividing by the 1 values. The VAR Grand_Total is not returning a value. Do you have any ideas on how to sum the values from the VAR Row_Average that get dynamcally generated based on the user slicer input?

Test1 =
VAR Total_Cost = SUM(CostUnpivot[TotalCost])

VAR Cell_Value =
    IF(
        HASONEVALUE(CostUnpivot[Value]) && HASONEVALUE(EstimateSummaryDataDetails[EstimateName]),
        Total_Cost
    )

VAR Column_Sum =
    IF(
        NOT HASONEVALUE(CostUnpivot[Value]) && HASONEVALUE(EstimateSummaryDataDetails[EstimateName]),
        SUMX(
            VALUES(CostUnpivot[Value]),
            CALCULATE(SUM(CostUnpivot[TotalCost]))
        )
    )

VAR Row_Average =
    IF(
        HASONEVALUE(CostUnpivot[Value]) && NOT HASONEVALUE(EstimateSummaryDataDetails[EstimateName]),
        DIVIDE(
            SUMX(
                VALUES(EstimateSummaryDataDetails[EstimateName]),
                CALCULATE(SUM(CostUnpivot[TotalCost]))
            ),
            COUNTROWS(
                FILTER(
                    VALUES(EstimateSummaryDataDetails[EstimateName]),
                    NOT(ISBLANK(CALCULATE(SUM(CostUnpivot[TotalCost]))))
                )
            )
        )
    )

VAR Grand_Total =
    IF(
        NOT HASONEVALUE(CostUnpivot[Value]) && NOT HASONEVALUE(EstimateSummaryDataDetails[EstimateName]),
        SUMX(
            VALUES(EstimateSummaryDataDetails[EstimateName]),
            Row_Average
        )
    )

RETURN
    SWITCH(
        TRUE(),
        HASONEVALUE(CostUnpivot[Value]) && HASONEVALUE(EstimateSummaryDataDetails[EstimateName]), Cell_Value,
        NOT HASONEVALUE(CostUnpivot[Value]) && HASONEVALUE(EstimateSummaryDataDetails[EstimateName]), Column_Sum,
        HASONEVALUE(CostUnpivot[Value]) && NOT HASONEVALUE(EstimateSummaryDataDetails[EstimateName]), Row_Average,
        NOT HASONEVALUE(CostUnpivot[Value]) && NOT HASONEVALUE(EstimateSummaryDataDetails[EstimateName]), Grand_Total,
        4
    )

EstimateName                                              A                    B                   Average
01000 General Conditions                 $32,296,542         $1                    $16,148,271
02000 Sitework                                  $14,605,437        $387,812          $7,496,625
03000 Concrete                                  $105,661,253      $346,324         $53,003,788
04000 Masonry                                  $3,954,466                                 $3,954,466
05000 Steel                                         $19,243,720       $784,354           $10,014,037
06000 Wood and Plastics                   $5,933,680         $228,463           $3,081,071
07000 Thermal and Moisture             $16,420,194      $1,512,613        $8,966,404
08000 Doors and Windows                $44,383,720      $726,806           $22,555,263
09000 Finishes                                    $57,686,886      $1,682,410        $29,684,648
10000 Specialties                                $3,012,361        $172,363           $1,592,362
11000 Equipment                               $4,834,714                                   $4,834,714
12000 Furnishings                               $1,299,201        $102,776           $700,988
13000 Special Construction                $3,982,324         $545,902           $2,264,113
14000 Conveying Systems                  $24,594,754                                 $24,594,754
15000 Mechanical                               $48,218,330       $1,415,628         $24,816,979
16000 Electrical                                   $36,147,413       $906,156            $18,526,784
17000 A&E Services                            $696,080            $372,645            $534,363
18000 Ancillary Construction Costs    $693,143                                      $693,143
Total                                                    $423,664,217     $9,184,253

[lbendlin · Accepted solution]

The correct-ish way is to use HASONEVALUE and a SWITCH statement

SWITCH(TRUE(),

HASONEVALUE(row) && HASONEVALUE(column),"Cell",

HASONEVALUE(row),"Column Total",

HASONEVALUE(column),"Row Total",

"Grand Total")

[MeyerRy]

I responded below. For some reason it keeps deleting my reply.

[lbendlin]

I saw your reply (briefly) - it was still looking like you did everything twice.  Refactor your code to ONLY use the SWITCH statement.

[MeyerRy]

I just found out I was way overcomplicating the measure. I was able to reproduce the same output with this much simplier measure. I am still having the issue with the grand total summing the rows. I made a new post to hopefully cause less confustion. I apperciate you taking the time to look at this.

Matrix Grant Total - Summing the Row Averages - Microsoft Fabric Community