Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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!
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

 

OneDrive

 

 

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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