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

7 Replies

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

    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's avatar
      Anonymous
      Not applicable
      8 years ago

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

        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