Skip to main content
cancel
Showing results for 
Search instead 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

Reply
holodan95
Helper II
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.
 
DATESHOP IDSTOCK (MEASURE)
2022.08.05 0:00XX1627
2022.06.24 0:00XX1626
2022.06.25 0:00XX1532
2022.06.28 0:00XX1443
2022.06.29 0:00XX1626
2022.06.30 0:00XX1699
 
Any idea how to solve this?
 
Thank you!
1 ACCEPTED SOLUTION
holodan95
Helper II
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.

View solution in original post

3 REPLIES 3
holodan95
Helper II
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.
ryan_mayu
Super User
Super User

@holodan95 

is this what you want?

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors