Weigthed Average per Catecogy and Subcategory

[DavidGTMS]

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.

IdPage	PAGE.NAME	IdCategory	CATEGORY.NAME
1	BANDAss MANAGEMENTss	1	Talent Agent
2	ArtistSpacesss Talentssss	1	Talent Agent
3	Avastss Sof	2	Software Company
4	University of Oxf	3	College & University
5	El Ibérico Gratuitos	4	Newspaper
5	El Ibérico Gratuitos	4	Newspaper
7	Mundo Deportivos	4	Newspaper
8	The Wall Street Journales	4	Newspaper
9	Ascot Racecourses	5	Stadium, Arena & Sports Venue
10	Rugby World Cupes	5	Stadium, Arena & Sports Venue
11	Wimbledonss	5	Stadium, Arena & Sports Venue
12	PadelClub Londons	5	Stadium, Arena & Sports Venue
13	Leicest Club	5	Stadium, Arena & Sports Venue
14	Rihanas	5	Artist
14	Rihanas	5	Musician/Band

And also what I need.

IdPage	PAGE.NAME	IdCategory	CATEGORY.NAME	Count Per Id_Category	Weighted Per Category	Count Per Id_Page	Weighted Per Page into Category
1	BANDAss MANAGEMENTss	1	Talent Agent			1	5
2	ArtistSpacesss Talentssss	1	Talent Agent	2	1,33	1	5
3	Avastss Sof	2	Software Company	1	0,67	1	10
4	University of Oxf	3	College & University	1	0,67	1	10
5	El Ibérico Gratuitos	4	Newspaper
5	El Ibérico Gratuitos	4	Newspaper			2	5
7	Mundo Deportivos	4	Newspaper			1	2,5
8	The Wall Street Journales	4	Newspaper	4	2,67	1	2,5
9	Ascot Racecourses	5	Stadium, Arena & Sports Venue			1	2
10	Rugby World Cupes	5	Stadium, Arena & Sports Venue			1	2
11	Wimbledonss	5	Stadium, Arena & Sports Venue			1	2
12	PadelClub Londons	5	Stadium, Arena & Sports Venue			1	2
13	Leicest Club	5	Stadium, Arena & Sports Venue	5	3,33	1	2
14	Rihanas	5	Artist	1	0,67		10
14	Rihanas	5	Musician/Band	1	0,67	2	10

[DavidGTMS · Accepted solution]

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

IdPage	PAGE.NAME	IdCategory	CATEGORY.NAME	Count Per Id_Category	Weighted Per Category	Count Per Id_Page	Weighted Per Page into Category
1	BANDAss MANAGEMENTss	1	Talent Agent			1	5
2	ArtistSpacesss Talentssss	1	Talent Agent	2	1,33	1	5
3	Avastss Sof	2	Software Company	1	0,67	1	10
4	University of Oxf	3	College & University	1	0,67	1	10
5	El Ibérico Gratuitos	4	Newspaper
5	El Ibérico Gratuitos	4	Newspaper			2	5
7	Mundo Deportivos	4	Newspaper			1	2,5
8	The Wall Street Journales	4	Newspaper	4	2,67	1	2,5
9	Ascot Racecourses	5	Stadium, Arena & Sports Venue			1	2
10	Rugby World Cupes	5	Stadium, Arena & Sports Venue			1	2
11	Wimbledonss	5	Stadium, Arena & Sports Venue			1	2
12	PadelClub Londons	5	Stadium, Arena & Sports Venue			1	2
13	Leicest Club	5	Stadium, Arena & Sports Venue	5	3,33	1	2
14	Rihanas	6	Artist	1	0,67		10
14	Rihanas	7	Musician/Band	1	0,67	2	10

[v-qiuyu-msft]

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 

[DavidGTMS]

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.

[v-qiuyu-msft]

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:

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]

Best Regards,
Qiuyun Yu 

[DavidGTMS · Accepted solution]

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

IdPage	PAGE.NAME	IdCategory	CATEGORY.NAME	Count Per Id_Category	Weighted Per Category	Count Per Id_Page	Weighted Per Page into Category
1	BANDAss MANAGEMENTss	1	Talent Agent			1	5
2	ArtistSpacesss Talentssss	1	Talent Agent	2	1,33	1	5
3	Avastss Sof	2	Software Company	1	0,67	1	10
4	University of Oxf	3	College & University	1	0,67	1	10
5	El Ibérico Gratuitos	4	Newspaper
5	El Ibérico Gratuitos	4	Newspaper			2	5
7	Mundo Deportivos	4	Newspaper			1	2,5
8	The Wall Street Journales	4	Newspaper	4	2,67	1	2,5
9	Ascot Racecourses	5	Stadium, Arena & Sports Venue			1	2
10	Rugby World Cupes	5	Stadium, Arena & Sports Venue			1	2
11	Wimbledonss	5	Stadium, Arena & Sports Venue			1	2
12	PadelClub Londons	5	Stadium, Arena & Sports Venue			1	2
13	Leicest Club	5	Stadium, Arena & Sports Venue	5	3,33	1	2
14	Rihanas	6	Artist	1	0,67		10
14	Rihanas	7	Musician/Band	1	0,67	2	10

[v-qiuyu-msft · Accepted solution]

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]

Best Regards,
Qiuyun Yu 

[DavidGTMS]

Thank you,

Best regards