Forum Discussion

sylvioros's avatar
sylvioros
Frequent Visitor
2 years ago

How to aggregate multiple records in a table?

Hi guys, I need your help. I've run out of attempts. It seems so simple, but I couldn't do it.

 

1) I  just need to sum/avg/max up all the values of a single record. 

 

For instance, I want 16 n_certif_B not 6 when I select a name in the table. Also, I want the average of '% total certif' for all 3 values (70,53%). How I do that? I've tried summarize(), but maybe because of the granularity of the table, it didn't aggregate when I select.

 

 

2) My main goal is:

  1. show the average of % total certif for the record (in the example above, Alessandra).
  2. show the total of certif A + B, but the calculation should be sum(n_certif_A) + max(n_certif_B).

 

That shouldn't be so tricky. 😞

 

Hope to get some help here. Thanks!!!

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    sylvioros Try the below Measures.
    Sum_n_certif_B = SUM(YourTable[n_certif_B])

    Avg_Total_Certif = AVERAGE(YourTable[% total certif])

    Max_n_certif_B = MAX(YourTable[n_certif_B])

    Avg_Total_Certif_Selected = CALCULATE( AVERAGE(YourTable[% total certif]), FILTER(YourTable, YourTable[Name] = SELECTEDVALUE(YourTable[Name])) )

    Total_Certif_A_B = CALCULATE( SUM(YourTable[n_certif_A]) + MAX(YourTable[n_certif_B]), FILTER(YourTable, YourTable[Name] = SELECTEDVALUE(YourTable[Name])) )


  • sylvioros's avatar
    sylvioros
    Frequent Visitor
    2 years ago

    Thanks for your reply Anonymous, but it didn't work.

     

    Please, note I'm working with relationships (2 tables in this scenario).

     

    Also the values come from a fact table where the records values are 0 or 1, so in the example above, the value of 6 are 6 rows with 1s values summed up. That's why I'm having trouble with MAX, because it usually shows 1. 

     

    Any other tip?