Forum Discussion

MeyerRy's avatar
MeyerRy
Frequent Visitor
2 years ago
Solved

Grand Total on Matrix Help

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.

 


Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    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")

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    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's avatar
      MeyerRy
      Frequent Visitor
      2 years ago

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

      • lbendlin's avatar
        lbendlin
        Super User
        2 years ago

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

  • MeyerRy's avatar
    MeyerRy
    Frequent Visitor
    2 years ago

    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