cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

Aggregations Not Working As Expected at Group Level

Example.PNG

 

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
Microsoft

Re: Aggregations Not Working As Expected at Group Level

Hi @Kevthebandit,

 

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.

1.PNG

 

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]

3.PNG

 

 

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.

View solution in original post

7 REPLIES 7
Highlighted
Microsoft
Microsoft

Re: Aggregations Not Working As Expected at Group Level

Hi @Kevthebandit,

 

Suppose your table structure is like:

1.PNG

 

Please create below measures:

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

Min = CALCULATE(Min(Event_Table[uploadEvents]),ALLEXCEPT(Event_Table,Event_Table[facilityid]))

Max = CALCULATE(MAX(Event_Table[uploadEvents]),ALLEXCEPT(Event_Table,Event_Table[facilityid]))

2.PNG

 

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
Advocate I
Advocate I

Re: Aggregations Not Working As Expected at Group Level

@v-yulgu-msft,

 

Thank you so much for your help!. I cannot seem to replicate your correct answer following your logic.

 

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.

 

Example 2.PNG

 

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
Microsoft

Re: Aggregations Not Working As Expected at Group Level

Hi @Kevthebandit,

 

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
Advocate I
Advocate I

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.

 

.Capture.PNG

Highlighted
Microsoft
Microsoft

Re: Aggregations Not Working As Expected at Group Level

Hi @Kevthebandit,

 

You could upload your .pbix file on OneDrive and paste the shared link here.

 

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
Advocate I
Advocate I

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
Microsoft

Re: Aggregations Not Working As Expected at Group Level

Hi @Kevthebandit,

 

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.

1.PNG

 

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]

3.PNG

 

 

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.

View solution in original post

Helpful resources

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 Kudoed Authors