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

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's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi Anonymous,

 

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

 

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