trankillity
New Member
- Joined
- Jan 16, 2015
- Messages
- 11
Hi all,
Not sure if this belongs here or under the Power BI subforum, but thought I'd plonk it here first.
I am analysing the gender and age demographics on shoppers at two of our stores using Power Pivot and want to be able to normalise the transactions at these stores so that they can be compared on a percent-by-percent basis.
The input data is individual transactions with a customer key linking back to a table with gender and age bracket in it. Below is the result of these groupings in a stacked column using Gender as the series and Store followed by AgeBrackets as the categories with transaction count as the values.
Now obviously I can show the transaction count as a "% of Grand Total" to get an accurate representation of the percentages as a whole over the entire data set, but what I really want to be able to do is show a normalised percentage of each of the age brackets grouped by store, so that across the different StoreType series, it adds up to 100% for each store. I can do this easily enough by showing the values as a "% of Parent Row Total", however this then breaks the percentage split between gender within a particular grouping by making it exactly what it should be, a percentage of the parent row's total as seen below.
So my question is, am I going to need to re-factor and normalise the data before I put it in a Pivot Chart? Or is there some sort of sacrifice that I can make to the Excel Gods to be able to get it to calculate the age brackets as a percentage of the parent row, but the gender split as a percentage of the age bracket grouping?
Not sure if this belongs here or under the Power BI subforum, but thought I'd plonk it here first.
I am analysing the gender and age demographics on shoppers at two of our stores using Power Pivot and want to be able to normalise the transactions at these stores so that they can be compared on a percent-by-percent basis.
The input data is individual transactions with a customer key linking back to a table with gender and age bracket in it. Below is the result of these groupings in a stacked column using Gender as the series and Store followed by AgeBrackets as the categories with transaction count as the values.
Now obviously I can show the transaction count as a "% of Grand Total" to get an accurate representation of the percentages as a whole over the entire data set, but what I really want to be able to do is show a normalised percentage of each of the age brackets grouped by store, so that across the different StoreType series, it adds up to 100% for each store. I can do this easily enough by showing the values as a "% of Parent Row Total", however this then breaks the percentage split between gender within a particular grouping by making it exactly what it should be, a percentage of the parent row's total as seen below.
So my question is, am I going to need to re-factor and normalise the data before I put it in a Pivot Chart? Or is there some sort of sacrifice that I can make to the Excel Gods to be able to get it to calculate the age brackets as a percentage of the parent row, but the gender split as a percentage of the age bracket grouping?
