Creating a measure that inserts both average and total in matrix?

[cheid_4838]

I have a matrix table that calculates various parameters and then averages them out (below).  I would like to create a measure that will put both the average and total for the date range shown.  Is it possible with the measure below to do this?  Thanks.

Total Charges - AVERAGE FOR WEEKLY MATRIX =

AVERAGEX(VALUES('Lookup Calendar'[End of Week]), [Total Charges])

[V-yubandi-msft]

Hi @cheid_4838 ,

Could you let us know if your issue has been resolved, or if you need any additional information or clarification? We are happy to help.

Thank you.

[V-yubandi-msft]

Hi @cheid_4838 ,

Both solutions from @Ritaf1983  and @johnbasha33  are correct, they simply use different approaches based on how you want your totals displayed.

1. If you want separate Total and Average columns, @johnbasha33  two measure method is ideal.

2. If you prefer showing Sum and Average as extra rows, @Ritaf1983  approach works well.

Either option is valid and will give you accurate results, so it depends on whether you want columns or summary rows.

Thank you both, @Ritaf1983  and @johnbasha33 , for your helpful input.

If you need more details, please let us know.

[Ritaf1983]

Hi @cheid_4838 

Unfortunately, Power BI does not support showing two types of totals (e.g. sum and average) in a matrix visual.

As a workaround, you can create a custom dates table that includes both the regular dates and additional rows for "Sum" and "Average" – like shown in the image.

Since the "Sum" and "Average" values are text, the whole column becomes text. To keep the correct sorting, you should add an index column.

In the fact table, duplicate the date column and change the data type to text so it can be used to link with the new dates table.

Create a relationship between the two tables using the text date columns – set this relationship as inactive.

Then, use a DAX measure like this to handle the logic for regular dates vs. "Sum" and "Average":

Measure_ =
if (SELECTEDVALUE('Dates with totals'[End of week (Sat.)]) IN {"Sum","Average"},
IF(SELECTEDVALUE('Dates with totals'[End of week (Sat.)]) = "Sum",
SUM('Data table'[Value]),
IF(SELECTEDVALUE('Dates with totals'[End of week (Sat.)]) = "Average",
AVERAGE('Data table'[Value]))),
CALCULATE(
SUM('Data table'[Value]),
USERELATIONSHIP('Data table'[End of week (Sat.) - Copy],'Dates with totals'[End of week (Sat.)])
))

Since the native matrix visual doesn’t support customizing total rows like in the Totals card, you can use conditional formatting to highlight the "Sum" and "Average" rows with a background color.

Example:

color = IF(SELECTEDVALUE('Dates with totals'[End of week (Sat.)]) IN {"Sum","Average"}, "#F2F2F2")

Apply this as a background color with conditional formatting.

Result :

The pbix is attached

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

[johnbasha33]

Hi @cheid_4838 
you can show both the weekly average and the total for the visible date range. Do it with two measures (and, if you want them side-by-side in the matrix, add a tiny “Metric” slicer/switch). Here’s the DAX you need:
Base measure (you likely already have)
Total Charges :=
-- your existing total measure
SUM('Fact'[ChargeAmount]) -- example only

Total for the current visible date range (ignores the week row in the matrix)
Total Charges – Date Range Total :=
CALCULATE(
[Total Charges],
ALLSELECTED('Lookup Calendar'[End of Week])
)

Weekly average over the visible weeks

(averages only weeks that actually have a value; include zero-weeks by wrapping with COALESCE([Total Charges], 0) inside AVERAGEX)

Total Charges – Weekly Average (Date Range) :=
VAR WeeksInScope :=
FILTER(
ALLSELECTED('Lookup Calendar'[End of Week]),
NOT ISBLANK( CALCULATE([Total Charges]) )
)
RETURN
AVERAGEX(WeeksInScope, CALCULATE([Total Charges]))

Use these two measures directly in your matrix if you’re OK having two value columns.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!