Help on summing and grouping with slicers

[TheJ010]

Hi all,

So my dateset looks like this.

Date_key	Year_week	Search_Phrase	Searches	Customer Type	Channel	CTR	Product Group
20230101	202310	powerbi	28	Retail	Web	10%	software
20230101	202310	powerbi	10	Business	Web	2%	software

I want to create a dashboard with

Search Phrase	Searches	CTR
powerbi	38	7,8% (= weighted average)

On the left I have slicers with 
1. Date_key
2. Year_week
3. Channel
4. Product Group

5. Customer Type

First question

- I need the searches amounts to be adjusted to the selections in the slicers

- AND identical searches phrases need to be summed so in this case 10 + 28

Second question
- I need the CTR to be weighted average of the CTRs

What variables do I need to add in my model to get this desired result?

Thanks for your help!

[Udbz]

So

 if I understand this correctly, you basically want a groupby behavior in tour dashboard and slicers to affect your report.

That should be the defut behavior when u drag and and drop your search term to a table and views in the values bucket.

when it comes to the getting weighted average, you must define wht are the weights based of?

• Say if ur basically weighing on basis of searches and ctr then u may use this
• SUMX(thistable, thistable[searches]*thistable[ctr])/Sum(Sum( searches)
• do note to divide by the sum of searches outside the SUMX .

 Basically for every row occuring aginst the search term ' a' you're multiplying searches and ctr, then after the multiplying is done ur dividing it by the total sum of searches.

If you find my answer helpful, an up vote would be appreciated 😊

[TheJ010]

This unfortunately did not work.

SUMX(TABLE, TABLE[SEARCHES]*TABLE[Clickrate])/SUM(SUM(AF_SEARCH_DB[SEARCHES]))

[visheshjain]

Hi @TheJ010,

Make 2 measures:

1. Sum of Phrase Searches =  SUM(Table, Phrase Searches)
2. Weighted Average = SUMX(Table, (Phrase Search* CTR)/[Sum of Phrase Searches]) i.e Use the first measure in the 2nd one.

Apologies, if my syntax is wrong, but you get the gist.

Create the drop everything on the table visual and slicers as you need and PBI will take care of the rest.

[TheJ010]

Too many arguments were passed to the SUM function. The maximum argument count for the function is 1.

I get this error argument.

[visheshjain · Accepted solution]

Hi @TheJ010,

You do not need mention SUM() function twice in your measure.

Define the total of the searches as Sum of Phrase Searches =  SUM(Table, Phrase Searches) and then use this measure as the denominator in the SUMX() function.