AVERAGE of a COUNTX measure

[holodan95]

Hello All,

I've been looking for a solution regarding my problem, however non of the finds worked. I have a measure, which calculates the
count of values, that are filtered. When I put it into a table, next to other values, like Shop ID, it nicely shows the numbers I need,
however when I want to calculate an average for these, it runs into a problem.

AVERAGEX(VALUES(table[table column]),[measure])
didn't work, because the values I have are not legit numbers, but the count of them.

I see what the problem is, because when I put the original measure into a card, it gives back a value
which is the total of the count. It doesn't know that I want to average values that are shown in a
table visual.

Example:

This is what you see in my visual, when I add a table with date, shop id and the original measure.

What I need, is a card that shows the average of the numbers in the table.

DATE	SHOP ID	STOCK (MEASURE)
2022.08.05 0:00	XX1	627
2022.06.24 0:00	XX1	626
2022.06.25 0:00	XX1	532
2022.06.28 0:00	XX1	443
2022.06.29 0:00	XX1	626
2022.06.30 0:00	XX1	699

Any idea how to solve this?

Thank you!

[holodan95 · Accepted solution]

Hi All, I finally solved my problem with the help of: https://community.powerbi.com/t5/Desktop/DAX-Help-Combine-Groupby-and-Filter-Functions/td-p/199645

Basically what I had to do, was to write the following measure:

STOCK =

AVERAGEX(

    FILTER(

        GROUPBY(Stock_table, Stock_table[Date],Stock_table[Shop ID], "TEST" ,    COUNTX(CURRENTGROUP(),Stock_table[Stock.value])),

         [MEASURE1]),

         [MEASURE1])

Basically what I do here, is calculating the average of a table that I've just made with groupby, filtering
it's values with [MEASURE1] measure.

I colored the [MEASURE1]'s, so it is easier to determin which function it affects.

Using GROUPBY basically made a virtual table, which I originally created as a visual.

[ryan_mayu]

@holodan95 

is this what you want?

Measure = sumx('Table',[stock])/DISTINCTCOUNT('Table'[DATE])

[holodan95]

Yes, 592,17 is what I'm looking for, but the table that I have is a visual, which works by measures, so I don't phisically have these values on hand, only if I order them by date, shop ID etc.. into a table measure.

In my case, I cannot sumx any column value, as it is a measure as well.

The original measure which gives me back the STOCK values:

STOCK = COUNTX(FILTER(STOCK_TABLE, STOCK_TABLE[STOCK] <> -999 && STOCK_TABLE[ARGUMENTl] = 1 ),STOCK_TABLE[STOCK])

From this, I have the values you see in the example : 627, 626 etc...

Without using a table, it would only give back the total of the values.