Forum Discussion

sshanssun's avatar
sshanssun
Icon for Helper I rankHelper I
1 year ago
Solved

DAX Measure does not give average in total column

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 colu...
Need Help
  • vojtechsima's avatar
    vojtechsima
    1 year ago

    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]

FarhanJeelani's avatar
FarhanJeelani
Icon for Super User rankSuper User
1 year ago

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!