Calculate average on table with multiple dimensions

[PSVSupp1913]

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?

[v-binbinyu-msft · Accepted solution]

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.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-binbinyu-msft · Accepted solution]

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.

[PSVSupp1913]

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] ).