Forum Discussion
Helper IVSneaky Weighted Average with Weights on Different Rows
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
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
8 Replies
- Greg_Deckler
Community Champion
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
danzrustSample 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.
- Ashish_Mathur
Super User
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.
v-jiascu-msftMicrosoft Employee
- danzrust
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
