Average is not working

[Anonymous]

Hi,

I have the numbers of customers of two months and want to show the average.

This visual is the sum of customers with code: 

customer[count_no] = IF(ISBLANK(customer[name]),BLANK(),1)

For the average I tried a measure:

count_AVG =
VAR _AverageTable =
    ADDCOLUMNS (
        VALUES ( 'Date'[Date].[Month] ),
        "count", COUNT ( customer[count_no] )
    )
RETURN
    AVERAGEX( _AverageTable, [count] )

Average should be 177, but my code shows 354. Always the sum

Does anyone have an idea what is wrong with my measure?

Thank you

[FreemanZ · Accepted solution]

hi @Anonymous 

try like:

count_AVG =
VAR _AverageTable =
    ADDCOLUMNS (
        VALUES ( 'Date'[Date].[Month] ),
        "count",

 CALCULATE(COUNT ( customer[count_no] ))

RETURN
    AVERAGEX( _AverageTable, [count] )

[Anonymous]

Awesome! It works. Thank you so much 🙂

[andhiii079845]

Tell us more about how you use this measure. Do you use a slicer?VALUES ( 'Date'[Date].[Month] ) seems you want to calulcated the average within a monthy group. 
Can you show the underlaying data for a given example, please. It is very difficult to understand the probem. 
Do you check the interaction between slicer and bar chart? 

Did the measure change, if you change the slicer. Can you please check if 354 is the average for the complete year 2022. I think in your measure miss a ALLSELECT(). 

[Anonymous]

yes, date (year and month) is used as a slicer, that also change the measure.

354 is always the sum of the selected months.

My data looks like:

Does that help?

[Anonymous]

yes, date (year and month) is used as a slicer, that also change the measure.

354 is always the sum of the selected months.

My data looks like:

Netamount	Month	Year	Clientno	Date	name	customer
10,00	1	1993	2534	01.01.1993	customer 1	1
20,00	1	1993	3475	01.01.1993	customer 2	1
20,00	1	1993	3475	01.08.1993
30,00	1	1993	5866	01.06.1993	customer 4	1
30,00	1	1993	2735	01.02.1993

Does that help?

[andhiii079845]

Why you add VALUES ( 'Date'[Date].[Month] ) in your DAX? Date and customer table have a relationship?

[Anonymous]

Hi, 

yes, they have. table "date" is my calender table. 
I used VALUES, because it does work for another table. Happy to know how it could work with something else