Forum Discussion
Calculate average on table with multiple dimensions
I have the following table:
D
| Date | Location | Client | Value |
| 2023-03-21 | A | 1 | 15 |
| 2023-03-21 | A | 2 | 10 |
| 2023-03-21 | A | 3 | 25 |
| 2023-03-21 | B | 1 | 30 |
| 2023-03-22 | A | 1 | 15 |
| 2023-03-22 | A | 2 | 10 |
| 2023-03-22 | A | 3 | 25 |
| 2023-03-22 | B | 1 | 30 |
| 2023-03-23 | A | 1 | 5 |
| 2023-03-23 | A | 2 | 5 |
| 2023-03-23 | B | 1 | 5 |
I want to create a visual with the average, depending on the selected data.
For example, I want to see the average of pallets per customer per day, if I select 2023-03-21 / 2023-03-22.
That should be:
| Client | Average |
| 1 | 45 |
| 2 | 10 |
| 3 | 25 |
I thought I should use Avg = sum( [Value]) / DISTINCTCOUNT ( Date ) but then I get this:
| Client | Avg |
| 1 | 22,5 |
| 2 | 10 |
| 3 | 25 |
What should I do to resolve this?
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.
3 Replies
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] ).
