cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## AVERAGE of a COUNTX measure

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!
1 ACCEPTED SOLUTION
Helper II

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.
3 REPLIES 3
Helper II

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.
Super User

is this what you want?

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

Proud to be a Super User!

Helper II

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.