Forum Discussion
Weighted data
You are more than welcome!
And yes, I think I understand what you are trying to accomplish. In this case, you can still use the approach I described earlier to create a weighted score for each food item, but instead of multiplying by the value column, you would multiply by the weighting variable for each moment in the day separately.Here's how you can do it:
Create a new calculated column for each moment in the day that you want to analyze (e.g., morning, afternoon, evening) using the same formula as before, but replacing "Value" with the name of the column that contains the frequency of each food item chosen during that moment, and "Weight" with the name of your weighting variable column.
Once you have created the calculated columns for each moment in the day, you can create a stacked or clustered bar chart using the calculated columns as the measures.
To display the scores in percentage, you can divide each calculated column by the total weight for that moment in the day. You can create a new calculated column for each moment in the day that calculates the total weight for that moment by using the following formula:
=SUM([Weight])
Then, create another calculated column for each moment in the day that calculates the weighted percentage for each food item by dividing the weighted score for that item by the total weight for that moment. You can use the following formula:
=DIVIDE([Weighted Score for Food Item], [Total Weight for Moment])
Finally, you can use the calculated columns that display the weighted percentages as the measures in your stacked or clustered bar chart. This will show you the percentage of times each food item was chosen during each moment in the day, weighted by your weighting variable.
I hope this helps! Let me know if you have any further questions.
Best regards,
Isaac Chavarria
If this post helps, then please consider Accept it as the solution and give Kudos to help the other members find it more quickly.
Hi Ishavarria,
Already a big thanks for your elabarotad answer on my question. I can and will indeed use this for a certain part of my report where I have numeric values. But the real difficultuy lays in the following aspect. I also have non numerical data that I want to weight in Clusterd or Stacked Bars. For example, respondents had to indacate for different moments in a day which type of food items they consumed. So, I want to the scores in % that each product was chosen in the different moments, but weighted by my wieghting variable. I hope that this is clear?
Already a big thanks.
You are more than welcome!
And yes, I think I understand what you are trying to accomplish. In this case, you can still use the approach I described earlier to create a weighted score for each food item, but instead of multiplying by the value column, you would multiply by the weighting variable for each moment in the day separately.
Here's how you can do it:
Create a new calculated column for each moment in the day that you want to analyze (e.g., morning, afternoon, evening) using the same formula as before, but replacing "Value" with the name of the column that contains the frequency of each food item chosen during that moment, and "Weight" with the name of your weighting variable column.
Once you have created the calculated columns for each moment in the day, you can create a stacked or clustered bar chart using the calculated columns as the measures.
To display the scores in percentage, you can divide each calculated column by the total weight for that moment in the day. You can create a new calculated column for each moment in the day that calculates the total weight for that moment by using the following formula:
=SUM([Weight])
Then, create another calculated column for each moment in the day that calculates the weighted percentage for each food item by dividing the weighted score for that item by the total weight for that moment. You can use the following formula:
=DIVIDE([Weighted Score for Food Item], [Total Weight for Moment])
Finally, you can use the calculated columns that display the weighted percentages as the measures in your stacked or clustered bar chart. This will show you the percentage of times each food item was chosen during each moment in the day, weighted by your weighting variable.
I hope this helps! Let me know if you have any further questions.
Best regards,
Isaac Chavarria
If this post helps, then please consider Accept it as the solution and give Kudos to help the other members find it more quickly.