Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
sylvioros
Frequent Visitor

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.

 

pbiquestion.jpg

 

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!!!

3 REPLIES 3
sylvioros
Frequent Visitor

Anyone?

 

sylvioros
Frequent Visitor

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

 

pbiquestion2.jpg

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?

Sandhya1234
Helper II
Helper II

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


Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.