Hi
I have a table that looks something like this.
SurveyNo | Year | Gender | ValidResponseFlag |
1 | 2015 | Male | Y |
2 | 2015 | Female | Y |
3 | 2015 | Female | Y |
4 | 2015 | Female | Y |
5 | 2015 | Male | Y |
6 | 2015 | Female | Y |
7 | 2015 | Female | Y |
8 | 2015 | Male | Y |
9 | 2015 | Female | N |
10 | 2019 | Female | Y |
11 | 2019 | Female | Y |
12 | 2019 | Female | Y |
13 | 2019 | Male | Y |
14 | 2019 | Female | Y |
15 | 2019 | Male | Y |
16 | 2019 | Female | Y |
17 | 2019 | Female | N |
I want to show a Bar Chart with 2 dimensions. -Gender and Year. Measure being the Percentage of Male and Femael over Total of the respective Years.
So on Bar chart it should show
Male should two bars side by side. 2015 with (3/8)37.5% and 2019 (2/7)28.57%.
Female should two bars side by side. 2015 with (5/8)62.5% and 2019 (5/7)71.42%.
I used the measure Count({<ValidResponseFlagInd={"Y"}>} ([Gender]))/COUNT(TOTAL{<ValidResponseFlagInd={"Y"}>} ([S.No]))
However, this is using denominator as total of 2015 and 2019. i.e.
Male 2015 with (3/15)20% and 2019 (2/15)13.33%
Female 2015 with (5/15)33.33% and 2019 (5/15) 33.33%
Please advise