cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dani42 Frequent Visitor
Frequent Visitor

case weighting in data

Hello,

 

as I am working in market research, our data which is always a sample from the population, is often weighted (nearly always). In statistical programs such as SPSS, one can define a variable which holds the case weights and activate that variable as a weighting variable (i.e. for cases overrepresentated in a sample, case weights are >1, whereas for cases underrepresented in a sample, case weights are <1). Is there a way to achieve that in Power BI?

Because, frankly, if there is no way for that, how could we ever use (raw) market research data in Power BI, because such data is very often weighted.

 

Any ideas would be helpful - thanks in advance,

Daniel

1 ACCEPTED SOLUTION

Accepted Solutions
dani42 Frequent Visitor
Frequent Visitor

Re: case weighting in data

Okay, after a few trials and errors, and googling the DAX formulas, I might have worked it out. so in my case the formula I needed was, for displaying the average of a scale value (index value, ranging from 0 to 100):

 

MyWeightedMeasure = SUMX('TableName'; 'TableName'[WeightColumnName] * 'TableName'[ColumnName]) / SUM('TableName'[WeightColumnName])

 

I realized that this "workaround" was only necessary for displaying such an average value. For percentages, i.e. frequencies of groups that are represented by different names in one column, such as "male/female", you only need to put your column in the legend field, and your weight column in the value-field (I'm in the german version, I hope I translated the words correctly). But be careful, I think that only works right if your weights sum up to the total n of cases in your table.

 

 

View solution in original post

9 REPLIES 9
Nhallquist Member
Member

Re: case weighting in data

What have you tried so far?  This is a bit outside my "sweet spot", but I would recommend looking at R to do some of this for you.  There is R integration for PowerBI and I think that is where you will get this type of functionality.  Use this Link to get started.  

 

 

dani42 Frequent Visitor
Frequent Visitor

Re: case weighting in data

Thanks for your suggestion. In R, weighting cases seems to be a bit more difficult than it is in SPSS, where you simply type "weight by var" and you're done. Also, if Power BI doesn't has weighting implemented, then I could also switch completely to R, I think. But my experience with both of the programs is very little...

teneuse Frequent Visitor
Frequent Visitor

Re: case weighting in data

Bumping this as I have the same question - Power BI does not look like it would be useful for analyzing/visualizing survey data if there's no option to set a case weight! I realize I could switch to R, but I was hoping that Power BI would allow me to produce visualizations quickly, and having to interface with R will make it slower than SPSS, Stata, etc.

teneuse Frequent Visitor
Frequent Visitor

Re: case weighting in data

I might have just answered my own question - here's how to do it in Tableau, which would work the same way in Power BI:

 

http://www.datarevelations.com/working-with-weighted-survey-data.html

 

dani42 Frequent Visitor
Frequent Visitor

Re: case weighting in data

This is an interesting link - but I don't get this working in PowerBI, I'm too "beginner" for that. Would you mind explaning the steps necessary for weighting cases in PowerBI? (e.g., the formula in the tableau tutorial is

SUM([Weight]) / TOTAL(SUM([Weight]))

Where do I type this formula? I tried creating a new measure, but it's not working (the word "total" doesn't exist as a formula in PowerBI, but that is only one of the problems). I was wondering where the connection to the target variable in the formula is? If I tried to analyse, say, gender proportions in my data, which would be unweighted 60% males 40% females, and the weighted values would be like 55% males and 45% females. I habe the required weight-Column in my data, but I can't imagine how to achieve that perhaps simple task...

 

Any help would be much appreciated,

Thanks,

Daniel

dani42 Frequent Visitor
Frequent Visitor

Re: case weighting in data

Okay, after a few trials and errors, and googling the DAX formulas, I might have worked it out. so in my case the formula I needed was, for displaying the average of a scale value (index value, ranging from 0 to 100):

 

MyWeightedMeasure = SUMX('TableName'; 'TableName'[WeightColumnName] * 'TableName'[ColumnName]) / SUM('TableName'[WeightColumnName])

 

I realized that this "workaround" was only necessary for displaying such an average value. For percentages, i.e. frequencies of groups that are represented by different names in one column, such as "male/female", you only need to put your column in the legend field, and your weight column in the value-field (I'm in the german version, I hope I translated the words correctly). But be careful, I think that only works right if your weights sum up to the total n of cases in your table.

 

 

View solution in original post

megm001 Regular Visitor
Regular Visitor

Re: case weighting in data

Hi! I have the same issue. Can you give an example of applying a weight to an entire table?

Thanks!

megm001 Regular Visitor
Regular Visitor

Re: case weighting in data

Hi! I have the same issue. Can you give an example of applying a weight to an entire table?

Thanks!

Anonymous
Not applicable

Re: case weighting in data

Bumping this, as I have been trying to follow your suggestion for using when frequencies of groups are represented by different names in the a column. I've been trying to use for my gender question, but get an error whenever I put the measure in the values bucket as suggested. Does anyone have any thoughts as to why this might be happening, and how to work around it?

 

Thanks in advance!


@dani42 wrote:

Okay, after a few trials and errors, and googling the DAX formulas, I might have worked it out. so in my case the formula I needed was, for displaying the average of a scale value (index value, ranging from 0 to 100):

 

MyWeightedMeasure = SUMX('TableName'; 'TableName'[WeightColumnName] * 'TableName'[ColumnName]) / SUM('TableName'[WeightColumnName])

 

I realized that this "workaround" was only necessary for displaying such an average value. For percentages, i.e. frequencies of groups that are represented by different names in one column, such as "male/female", you only need to put your column in the legend field, and your weight column in the value-field (I'm in the german version, I hope I translated the words correctly). But be careful, I think that only works right if your weights sum up to the total n of cases in your table.

 

 


 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors