DAX Measure does not give average in total column

[sshanssun]

Hi - I have worker's data created using matrix table with DAX measure. However, the row and column total does not gives me the average value and showing total value. How do I change both row and column sum total in to a average total.

Below is the current value and red color is the expected value, also attached the PBIX file link. Thanks in advance for looking into this.

PBIX file link: https://drive.google.com/file/d/18I5dSQYvJiRGoNvzSsna-SflB33HyPxx/view?usp=drivesdk

[FarhanJeelani]

To calculate average values for both row and column totals in a Power BI matrix instead of summing them, you can modify your DAX measure to handle averages separately for the totals.

Here is a DAX approach you can try:

1. Identify If in Total Row/Column: Use the `ISINSCOPE` function to check if a value is within a row/column total context.

2. Modify the Measure for Averages: Adjust your DAX measure to compute the average in the row or column totals instead of summing.

Here's an example of how you might write this DAX measure to achieve the average totals:

DAX
Average Measure =
VAR RowTotal =
IF(
NOT ISINSCOPE('YourTable'[Emp Name]),
AVERAGEX(
VALUES('YourTable'[Emp Name]),
[YourMeasure]
),
[YourMeasure]
)

VAR ColumnTotal =
IF(
NOT ISINSCOPE('YourTable'[Date Column]),
AVERAGEX(
VALUES('YourTable'[Date Column]),
[YourMeasure]
),
[YourMeasure]
)

RETURN
IF(
NOT ISINSCOPE('YourTable'[Emp Name]) && NOT ISINSCOPE('YourTable'[Date Column]),
AVERAGEX(
ALLSELECTED('YourTable'),
[YourMeasure]
),
IF(
NOT ISINSCOPE('YourTable'[Emp Name]),
RowTotal,
ColumnTotal
)
)

In this DAX measure:

- RowTotal computes the average for each employee across selected dates.
- ColumnTotal computes the average for each date across employees.
- The final `RETURN` statement adjusts the display based on whether it’s a row or column total.

Replace `[YourMeasure]` with the actual measure you’re using for the percentages or values in the table. This measure should give you average values in both row and column totals.

Let me know if this helps or if you'd like more guidance on any part of the DAX code!

[vojtechsima]

Hello, @sshanssun ,
I have troubles opening your file, it feels like it's damaged, can you try to share it again?

Generally, Totals in PBI doesn't show SUM as in Total in Excel for example. Total generally shows the same measure just without the filter context of the columns or rows. If you want Average, you gotta use average function or if you wanna sum but in Total you want average, you need to play with functions like "HASONEVALUE" or "HASONEFILTER" and then specify what should happen when it means the condition.

[sshanssun]

Hi, Thank you for the response, please see if the below link is working to access pbix file,

https://drive.google.com/file/d/10Ln1kpnttDV-uHxzaUg25ZjEjKqlHPpU/view?usp=drivesdk

[vojtechsima · Accepted solution]

Hello, @sshanssun ,

the easiest solution would be to utilize a virtual table to first summarize it and then do average over it.

Two new measures like this:

EfficencyFixed = 
var _virtualTable = SUMMARIZE(Data, Data[Emp Name], Data[Date].[Day], "value", [Efficiency])
var _average = AVERAGEX( _virtualTable, [value])

return _average

ProdPerHourFixed = 
var _virtualTable = SUMMARIZE(Data, Data[Emp Name], Data[Date].[Day], "value", [Prod per hour])
var _average = AVERAGEX( _virtualTable, [value])

return _average

Please note, this will only work with Emp Name and Day of Date, as this is kinda hardcoded sumarization. It would be also better to have Calendar, so you don't have use [Date].[Day]

[sshanssun]

Hi @vojtechsima  - It worked like a charm. Thank you so much!

[vojtechsima]

@sshanssun  happy to help. kudos appretiated.