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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
DavidGTMS
Helper I
Helper I

Weigthed Average per Catecogy and Subcategory

Hello everyone,

I'm trying to get the average over 10, by categories and subcategories. I give you the reference table. And also what I need.

Thank you very much.

 

IdPagePAGE.NAMEIdCategoryCATEGORY.NAME
1BANDAss MANAGEMENTss1Talent Agent
2ArtistSpacesss Talentssss1Talent Agent
3Avastss Sof2Software Company
4University of Oxf3College & University
5El Ibérico Gratuitos4Newspaper
5El Ibérico Gratuitos4Newspaper
7Mundo Deportivos4Newspaper
8The Wall Street Journales4Newspaper
9Ascot Racecourses5Stadium, Arena & Sports Venue
10Rugby World Cupes5Stadium, Arena & Sports Venue
11Wimbledonss5Stadium, Arena & Sports Venue
12PadelClub Londons5Stadium, Arena & Sports Venue
13Leicest Club5Stadium, Arena & Sports Venue
14Rihanas5Artist
14Rihanas5

Musician/Band

 

 

 

And also what I need.

 

IdPagePAGE.NAMEIdCategoryCATEGORY.NAMECount Per Id_CategoryWeighted Per CategoryCount Per Id_PageWeighted Per Page into Category
1BANDAss MANAGEMENTss1Talent Agent  15
2ArtistSpacesss Talentssss1Talent Agent21,3315
3Avastss Sof2Software Company10,67110
4University of Oxf3College & University10,67110
5El Ibérico Gratuitos4Newspaper    
5El Ibérico Gratuitos4Newspaper  25
7Mundo Deportivos4Newspaper  12,5
8The Wall Street Journales4Newspaper42,6712,5
9Ascot Racecourses5Stadium, Arena & Sports Venue  12
10Rugby World Cupes5Stadium, Arena & Sports Venue  12
11Wimbledonss5Stadium, Arena & Sports Venue  12
12PadelClub Londons5Stadium, Arena & Sports Venue  12
13Leicest Club5Stadium, Arena & Sports Venue53,3312
14Rihanas5Artist10,67 10
14Rihanas5Musician/Band10,67210
2 ACCEPTED SOLUTIONS

Hi,
 
Everything almost right. I was right that the last 2 rows in "id category", are 6 and 7. But in the column calculated "Weighted Per Page into Category" these rows with "index" 14 and 15, your result should be 10 in each row. Since it is your weight within the category.
 
Is it possible to do that??
 
Thanks again

 

IdPagePAGE.NAMEIdCategoryCATEGORY.NAMECount Per Id_CategoryWeighted Per CategoryCount Per Id_PageWeighted Per Page into Category
1BANDAss MANAGEMENTss1Talent Agent  15
2ArtistSpacesss Talentssss1Talent Agent21,3315
3Avastss Sof2Software Company10,67110
4University of Oxf3College & University10,67110
5El Ibérico Gratuitos4Newspaper    
5El Ibérico Gratuitos4Newspaper  25
7Mundo Deportivos4Newspaper  12,5
8The Wall Street Journales4Newspaper42,6712,5
9Ascot Racecourses5Stadium, Arena & Sports Venue  12
10Rugby World Cupes5Stadium, Arena & Sports Venue  12
11Wimbledonss5Stadium, Arena & Sports Venue  12
12PadelClub Londons5Stadium, Arena & Sports Venue  12
13Leicest Club5Stadium, Arena & Sports Venue53,3312
14Rihanas6Artist10,67 10
14Rihanas7Musician/Band10,67210

View solution in original post

Hi @DavidGTMS,

 

You can add a new column:

 

Count Per Id_Page into category = var c1=CALCULATE(COUNT(Table2[IdPage]),FILTER('Table2','Table2'[IdPage]=EARLIER(Table2[IdPage])))
return
IF('Table2'[Index]=CALCULATE(MAX('Table2'[Index]),FILTER('Table2','Table2'[IdPage]=EARLIER('Table2'[IdPage])&& Table2[IdCategory]=EARLIER(Table2[IdCategory]))),c1,BLANK())

 

Then modify the Weighted Per Page into Category column below:

 

Weighted Per Page into Category = DIVIDE(10,CALCULATE(SUM(Table2[Count Per Id_Page into category]),FILTER('Table2','Table2'[IdCategory]=EARLIER(Table2[IdCategory]))))*Table2[Count Per Id_Page into category]

 

q4.PNG

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-qiuyu-msft
Community Support
Community Support

Hi @DavidGTMS,

 

Before sharing DAX, there are some question which would like to confirm from you. 

 

1. The column "Count Per Id_Category" should calculate the count for each Id Category. But why you return 1 for last two rows. Their IdCategory is 5, why don't calculate with other IdCategory value 5? 

 

2. What's the logic to calculate "Weighted Per Category" and "Weighted Per Page into Category"? 

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

hi,

 

Sorry, but the last 2 rows would be category 6. And yes, the goal is to count by Id_Category, calculate the weight by category on 10, and within each category calculate the weight of each Page.Name on 10.

Thank you very much.

 

A greeting.

Hi @DavidGTMS,

 

I think last two rows should be category 6 and 7, otherwise the last two rows of  Count Per Id_Category column shouldn't be 1. 

 

Please go to Query Editor, add a index column from 1. Then create calculated columns below:

 

a1.PNG

 

Count Per Id_Category = var c=CALCULATE(COUNT(Table2[IdCategory]),ALLEXCEPT(Table2,Table2[IdCategory]))
return
IF('Table2'[Index]=CALCULATE(MAX('Table2'[Index]),FILTER('Table2','Table2'[IdCategory]=EARLIER('Table2'[IdCategory]))),c,BLANK())

 

Weighted Per Category = DIVIDE(10,SUM(Table2[Count Per Id_Category]))*Table2[Count Per Id_Category]

 

Count Per Id_Page = var c1=CALCULATE(COUNT(Table2[IdPage]),FILTER('Table2','Table2'[IdPage]=EARLIER(Table2[IdPage])))
return
IF('Table2'[Index]=CALCULATE(MAX('Table2'[Index]),FILTER('Table2','Table2'[IdPage]=EARLIER('Table2'[IdPage]))),c1,BLANK())

 

Weighted Per Page into Category = DIVIDE(10,CALCULATE(SUM(Table2[Count Per Id_Page]),FILTER('Table2','Table2'[IdCategory]=EARLIER(Table2[IdCategory]))))*Table2[Count Per Id_Page]

 

z1.PNG

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi,
 
Everything almost right. I was right that the last 2 rows in "id category", are 6 and 7. But in the column calculated "Weighted Per Page into Category" these rows with "index" 14 and 15, your result should be 10 in each row. Since it is your weight within the category.
 
Is it possible to do that??
 
Thanks again

 

IdPagePAGE.NAMEIdCategoryCATEGORY.NAMECount Per Id_CategoryWeighted Per CategoryCount Per Id_PageWeighted Per Page into Category
1BANDAss MANAGEMENTss1Talent Agent  15
2ArtistSpacesss Talentssss1Talent Agent21,3315
3Avastss Sof2Software Company10,67110
4University of Oxf3College & University10,67110
5El Ibérico Gratuitos4Newspaper    
5El Ibérico Gratuitos4Newspaper  25
7Mundo Deportivos4Newspaper  12,5
8The Wall Street Journales4Newspaper42,6712,5
9Ascot Racecourses5Stadium, Arena & Sports Venue  12
10Rugby World Cupes5Stadium, Arena & Sports Venue  12
11Wimbledonss5Stadium, Arena & Sports Venue  12
12PadelClub Londons5Stadium, Arena & Sports Venue  12
13Leicest Club5Stadium, Arena & Sports Venue53,3312
14Rihanas6Artist10,67 10
14Rihanas7Musician/Band10,67210

Hi @DavidGTMS,

 

You can add a new column:

 

Count Per Id_Page into category = var c1=CALCULATE(COUNT(Table2[IdPage]),FILTER('Table2','Table2'[IdPage]=EARLIER(Table2[IdPage])))
return
IF('Table2'[Index]=CALCULATE(MAX('Table2'[Index]),FILTER('Table2','Table2'[IdPage]=EARLIER('Table2'[IdPage])&& Table2[IdCategory]=EARLIER(Table2[IdCategory]))),c1,BLANK())

 

Then modify the Weighted Per Page into Category column below:

 

Weighted Per Page into Category = DIVIDE(10,CALCULATE(SUM(Table2[Count Per Id_Page into category]),FILTER('Table2','Table2'[IdCategory]=EARLIER(Table2[IdCategory]))))*Table2[Count Per Id_Page into category]

 

q4.PNG

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you,

 

Best regards

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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