cancel
Showing results for
Did you mean:
Highlighted

## Aggregations Not Working As Expected at Group Level

In this example, I am expecting to see for facilityid = 1511:

Avg = 5

Min = 2

Max = 8

How can I get my aggregations formulas to generate this accordingly? Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft

## Re: Aggregations Not Working As Expected at Group Level

In your scenario, the average value '1' is correct. As you summed up the [uploadEvents], from the visual, we can see 6 rows for 1511, actually, it contains 30 rows. You can directly choose "Average" to aggregate [uploadEvents]. There is no need to use measures.

If you want to get the result in my original post, please refer to below formulas.

```Sum =
CALCULATE (
SUM ( 'Usage (Fact Table)'[uploadEvents] ),
FILTER (
ALLSELECTED ( 'Usage (Fact Table)' ),
'Usage (Fact Table)'[facilityId] = MAX ( 'Usage (Fact Table)'[facilityId] )
)
)

count rows =
CALCULATE (
DISTINCTCOUNT ( 'Usage (Fact Table)'[k_effectiveDate] ),
FILTER (
ALLSELECTED ( 'Usage (Fact Table)' ),
'Usage (Fact Table)'[facilityId] = MAX ( 'Usage (Fact Table)'[facilityId] )
)
)

Avg Calc = [Sum]/[count rows]```

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
Highlighted
Microsoft

## Re: Aggregations Not Working As Expected at Group Level

Suppose your table structure is like:

```Average = CALCULATE(AVERAGE(Event_Table[uploadEvents]),ALLEXCEPT(Event_Table,Event_Table[facilityid]))

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted

## Re: Aggregations Not Working As Expected at Group Level

Do you know why my Avg Calc result does not match yours? My table structure looks like the same as yours, the formula looks the same.

Avg Calc = CALCULATE(AVERAGE('Usage (Fact Table)'[uploadEvents]),ALLEXCEPT('Usage (Fact Table)','Usage (Fact Table)'[facilityId]))

Avg Usage = AVERAGEX( VALUES('Date'[k_effectiveDate]), [Usage By Day])

Usage By Day = sum('Usage (Fact Table)'[uploadEvents])

Highlighted
Microsoft

## Re: Aggregations Not Working As Expected at Group Level

From your formula, it looks correct. Could you please share your pbix file so that I can check for you?

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted

## Re: Aggregations Not Working As Expected at Group Level

I have been trying to figure out how to do that. I have not found a helpful forum post yet or see a button for me to select in order to accomplish. I have a pro version. Any suggestions? I will keep looking. Thanks.

.

Highlighted
Microsoft

## Re: Aggregations Not Working As Expected at Group Level

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted

## Re: Aggregations Not Working As Expected at Group Level

Thanks for your help! You should be able to get into the PBIX file within my One Drive now!

OneDrive

Highlighted
Microsoft

## Re: Aggregations Not Working As Expected at Group Level

In your scenario, the average value '1' is correct. As you summed up the [uploadEvents], from the visual, we can see 6 rows for 1511, actually, it contains 30 rows. You can directly choose "Average" to aggregate [uploadEvents]. There is no need to use measures.

If you want to get the result in my original post, please refer to below formulas.

```Sum =
CALCULATE (
SUM ( 'Usage (Fact Table)'[uploadEvents] ),
FILTER (
ALLSELECTED ( 'Usage (Fact Table)' ),
'Usage (Fact Table)'[facilityId] = MAX ( 'Usage (Fact Table)'[facilityId] )
)
)

count rows =
CALCULATE (
DISTINCTCOUNT ( 'Usage (Fact Table)'[k_effectiveDate] ),
FILTER (
ALLSELECTED ( 'Usage (Fact Table)' ),
'Usage (Fact Table)'[facilityId] = MAX ( 'Usage (Fact Table)'[facilityId] )
)
)

Avg Calc = [Sum]/[count rows]```

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### August Community Highlights

Check out a full recap of the month!

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors