Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

Hi @Anonymous,

 

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
v-yulgu-msft
Employee
Employee

Hi @Anonymous,

 

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.
Anonymous
Not applicable

@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])

Hi @Anonymous,

 

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.
Anonymous
Not applicable

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

Hi @Anonymous,

 

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.
Anonymous
Not applicable

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

 

OneDrive

 

 

Hi @Anonymous,

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.