Sneaky Weighted Average with Weights on Different Rows

[danzrust]

Hi guys,

I have a wierd scenario.

This is my raw data:

I want to create a new calculated metric Search Imp. Share (yes, same name as in the table), which would show 80.545 regardless of what I put into rows while taking into account Impressions as weights.

E.g. I would use SubSegmentNew in rows and drop my new metric Search Imp. Share into values and it would show 80.545 as result. This needs to work regardless what kind of segmentation I setup. There more segmentation columns in my raw data.

What needs to happen is that the impressions need to be used as weight and somehow used for mulitplying the Search Imp. Share and then divide by Impressions again.

I tried SUMX(Data, [Impressions] * [Search. Imp Share])/SUM(Data[Impressions]) but it shows zero since SUMX evaluates same rows only while my Impressions are on different rows than the Search Imp. Share.

The reason why my Search Imp. Share is not split by DEVICE is simply because the system where it's coming from does not provide such split and the device is "not available.

Any ideas?

DZ

[v-jiascu-msft]

Hi @danzrust,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[danzrust · Accepted solution]

I figure it out at the end - I was able to join to "weights" within the raw data. So I could then do proper weighting on row level in DAX.

DZ

[v-jiascu-msft]

Thanks for sharing. @danzrust

Best Regards,

Dale

[Ashish_Mathur]

Hi,

Why not take the max figure from the Search Imp. Share column?

[danzrust]

Because Max would not work for different segmentation levels. There are more than 20 segmentation columns in my table. So there needs to be some totaling and weighting... And Impressions are weights for Search Imp. Share

As I said, if the Search Imp. Share numbers were on the same row as Impressions, you would do SUMX(Data,Data[Impressions]*Data[Search Imp. Share])/SUM(Data[Impressions]) but you this approach does not work here.

DZ

[Ashish_Mathur]

Hi,

Drag SubSegmentNew field to the row labels of the Table visual and then write this measure

=MAX(Data[Search Imp. Share])

No weighing would work here.

[Greg_Deckler]

Confused. Please provide your expected output for the sample data provided. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[danzrust]

Sample output would be a pivot table: 

SubSegmentNew  | Impressions | Search Imp. Share

Brand | 722 | 80.545

Another table could be 

Date  | Impressions | Search Imp. Share

8/23/2018 | 722 | 80.545

Basically, I want to create all kinds of pivot tables and the Search Imp. Share cannot be just  a simple sum of all values in that column. It needs to be weighted by impressions but the problem is that Impressions and Search Imp. Share are on different rows.

If they were on the same rows than I would be able to do the SUMX trick, but it's not possible here.