Forum Discussion
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.
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...
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.
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
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
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.