topic Re: Grand Total on Matrix Help in DAX Commands and Tips

Grand Total on Matrix Help

MeyerRy — Mon, 03 Jun 2024 16:21:46 GMT

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.

Re: Grand Total on Matrix Help

lbendlin — Sun, 02 Jun 2024 23:05:54 GMT

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

Re: Grand Total on Matrix Help

MeyerRy — Mon, 03 Jun 2024 14:59:33 GMT

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

Re: Grand Total on Matrix Help

MeyerRy — Mon, 03 Jun 2024 15:00:00 GMT

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

Re: Grand Total on Matrix Help

lbendlin — Mon, 03 Jun 2024 16:23:31 GMT

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

Re: Grand Total on Matrix Help

MeyerRy — Mon, 03 Jun 2024 16:32:44 GMT

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