topic Re: Average is not working in DAX Commands and Tips

Average is not working

Anonymous — Sun, 05 Mar 2023 20:00:15 GMT

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

Re: Average is not working

andhiii079845 — Sun, 05 Mar 2023 20:04:49 GMT

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

Re: Average is not working

Anonymous — Sun, 05 Mar 2023 20:12:16 GMT

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

Re: Average is not working

andhiii079845 — Sun, 05 Mar 2023 20:27:50 GMT

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

Re: Average is not working

Anonymous — Sun, 05 Mar 2023 20:58:19 GMT

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?

Re: Average is not working

Anonymous — Sun, 05 Mar 2023 21:00:11 GMT

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?

Re: Average is not working

FreemanZ — Mon, 06 Mar 2023 00:17:11 GMT

hi Anonymous

try like:

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

CALCULATE(COUNT ( customer[count_no] ))

RETURN
AVERAGEX( _AverageTable, [count] )

Re: Average is not working

Anonymous — Mon, 06 Mar 2023 06:08:36 GMT

Awesome! It works. Thank you so much 🙂