Forum Discussion
Calculate average on table with multiple dimensions
Hi PSVSupp1913 ,
Please try below steps:
1. below is my test table
Table:
2. create a measure with below dax formula
Avg = VAR min_date = MINX ( 'Table', [Date] ) VAR max_date = MAXX ( 'Table', [Date] ) VAR tmp = FILTER ( 'Table', 'Table'[Date] >= min_date && 'Table'[Date] <= max_date ) VAR tmp1 = CALCULATETABLE ( VALUES ( 'Table'[Date] ), tmp ) VAR _val = SUMX ( tmp, [Value] ) VAR _ctn = COUNTROWS ( tmp1 ) RETURN DIVIDE ( _val, _ctn )3. add a table and slicer visual
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi PSVSupp1913 ,
Please try below steps:
1. below is my test table
Table:
2. create a measure with below dax formula
Avg =
VAR min_date =
MINX ( 'Table', [Date] )
VAR max_date =
MAXX ( 'Table', [Date] )
VAR tmp =
FILTER ( 'Table', 'Table'[Date] >= min_date && 'Table'[Date] <= max_date )
VAR tmp1 =
CALCULATETABLE ( VALUES ( 'Table'[Date] ), tmp )
VAR _val =
SUMX ( tmp, [Value] )
VAR _ctn =
COUNTROWS ( tmp1 )
RETURN
DIVIDE ( _val, _ctn )
3. add a table and slicer visual
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks, that works as well!
But I found another solution. By adding a measure in the data table, Total=sum([Value]), the Dax average worked. Avg = [Total] / DISTINCTCOUNT ( [Date] ).