Aggregations Not Working As Expected at Group Level

[Anonymous]

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!

[v-yulgu-msft]

Hi @Anonymous,

Suppose your table structure is like:

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

Best regards,
Yuliana Gu

[Anonymous]

@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.

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

[v-yulgu-msft]

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

[Anonymous]

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.

.

[v-yulgu-msft]

Hi @Anonymous,

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

Regards,
Yuliana Gu

[Anonymous]

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

OneDrive

[v-yulgu-msft · 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.

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