Forum Discussion
Case weighting in power BI
Hello,
I have been running into a bit of a wall lately, and seems others are two, when using survey data in powerBI. My problem is that I need to weight my data by case (respondent). I have a variable in my table which is the weight each respondent carries (their sum adds up to the total number of responsdents). I need to use this to up or down weight each respondent when I plot questions so our data matches targets (such as nat rep samples). I realise this isnt very straightforward in BI and most of my responses are not numerical (ie. are yes, no, male, female etc.).
Does anyone have any suggestions for the best way to go about weighting my variables in BI? Currently I am trying to follow the solution in https://community.powerbi.com/t5/Desktop/case-weighting-in-data/td-p/51599, but having very little luck getting it to work on a simple column chart showing percentages of those male and female. Using this DAX code:
This is something thats very common in market research and very easy to implement in other packages such as SPSS and Q, so seems strange theres no option to weight by case here.
Any help or advice would be very much appreciated!
I use the GROUPBY function to weight consumer research data in PBI, and the CALCULATE function to be able to apply filters so that I can use the break columns to dynamically calculate the base size - usually I create a numerator and a denominator to make the DAX easier to read and edit later if needs be:
Base size denom = CALCULATE(SUMX(GROUPBY([name of table], [respondent ID], "GroupName", AVERAGEX(CURRENTGROUP(), [Weight column])), GroupName), ALLEXCEPT([name of table], [whatever columns you want to use as breaks in slicers, plus the column with the question in]))
Numerator = SUMX([name of table], [Weight column]
%age of respondents = [Numerator] / [Base size denom]
If you then have the %age of respondents measure in the values of your chart, and the response codes in the values, this will calculate the weighted total of respondents for each question / break combo that you have in slicers (or however else you choose to filter the survey).
12 Replies
I use the GROUPBY function to weight consumer research data in PBI, and the CALCULATE function to be able to apply filters so that I can use the break columns to dynamically calculate the base size - usually I create a numerator and a denominator to make the DAX easier to read and edit later if needs be:
Base size denom = CALCULATE(SUMX(GROUPBY([name of table], [respondent ID], "GroupName", AVERAGEX(CURRENTGROUP(), [Weight column])), GroupName), ALLEXCEPT([name of table], [whatever columns you want to use as breaks in slicers, plus the column with the question in]))
Numerator = SUMX([name of table], [Weight column]
%age of respondents = [Numerator] / [Base size denom]
If you then have the %age of respondents measure in the values of your chart, and the response codes in the values, this will calculate the weighted total of respondents for each question / break combo that you have in slicers (or however else you choose to filter the survey).
Hi stevencape ,
Thanks for the response, thats great and I'll try it out! Do you also have any work arounds for when you use non-numeric data, such as male/female? Or do you change these to numeric values when you weight them?
Thanks again for your response!
You're welcome! The only numeric data I'd expect to have from consumer research data would be the weighting, so if if I was looking at the ratio of male to female respondents in a survey I would be summing up the total weights for male and dividing that by the sum of all the weights, and then comparing that to the sum of the total weights for female and dividing that by the total weights.
Here's an example of the DAX I posted earlier being used with respondent level data from a few surveys to show the weighted responses - this data has columns for the question, the response (both text), the weights (decimal) and one for each of the breaks in the slicers (text). The value field is from the DAX.
stevencape wrote:I use the GROUPBY function to weight consumer research data in PBI, and the CALCULATE function to be able to apply filters so that I can use the break columns to dynamically calculate the base size - usually I create a numerator and a denominator to make the DAX easier to read and edit later if needs be:
Base size denom = CALCULATE(SUMX(GROUPBY([name of table], [respondent ID], "GroupName", AVERAGEX(CURRENTGROUP(), [Weight column])), GroupName), ALLEXCEPT([name of table], [whatever columns you want to use as breaks in slicers, plus the column with the question in]))
Numerator = SUMX([name of table], [Weight column]
%age of respondents = [Numerator] / [Base size denom]
If you then have the %age of respondents measure in the values of your chart, and the response codes in the values, this will calculate the weighted total of respondents for each question / break combo that you have in slicers (or however else you choose to filter the survey).
Also, (sorry for asking more questions!) but what would you use as your "group name" variable? Would that be age? And then I understand putting the measure in the values of your chart, but would the response codes go in the legend?
Thanks again!
Apologies - I meant "put the response codes in the axis" rather than the values. Though it would work in the legend too - as long as you're filtering in some way with the response codes.
And the "Group name" is any name that you specify - it's a reference created by the GROUPBY function that then becomes the expression used by the SUMX function.
Hello stevencape! I have just come across this solution as I am trying to visualise survey responses in Power BI but I am very new to Power BI and DAX. I have used your code for "Base size denom" but I'm getting the following error message: "MdxScript(Model) (3,138) Calculation error in measure 'Calculations'[Base_size_denom]: The function SUMX cannot work with values of type String".
Most of my data is text as it is consumer research data, could you help me understand what's going wrong please?
