cancel
Showing results for
Did you mean:
Regular Visitor

Proportion and Percent Change by Year and Group

Hello,

I have the following data table in Power BI in a table:

 Year Gender Members 2016 F 269391 2017 F 255061 2018 F 251992 2016 M 258512 2017 M 248718 2018 M 244728

I am looking to find the proportion of members by gender by year as a column in the table. So this is the result I am looking for (i.e. row 1 = 269391/ (269391 + 258512):

 Proportion 0.510303976 0.506295419 0.507311967 0.489696024 0.493704581 0.492688033

Additionally, I am looking to get the % change of members from the previous year within each gender. So you would get the following.

 Previous_Year_Members Change_From_Previous_Year %_Change_From_Previous_Year 269391 -14330 -5.3% 255061 -3069 -1.2% 258512 -9794 -3.8% 248718 -3990 -1.6%

I had another table that was just grouped by year and was able to get this information by creating a lagged column for the members, but that doesn't seem to work when there is two fields that the data is grouped on. I also can get this information in the visuals through the 100% stacked bar chart (for proportion of members by gender and year) and then using the time intelligence year over year change, but I cannot seem to replicate it so that it is in the underlying table.

If anyone has any guidance that would be greatly appreciated!

1 ACCEPTED SOLUTION
Community Support

Hi @cbiddle,

You can try to use the following measure formulas if they meet for your requirement:

``````Gender % by Year =
CALCULATE (
SUM ( 'Table'[Members] ),
ALLSELECTED ( 'Table' ),
VALUES ( 'Table'[Year] ),
VALUES ( 'Table'[Gender] )
)
/ CALCULATE (
SUM ( 'Table'[Members] ),
ALLSELECTED ( 'Table' ),
VALUES ( 'Table'[Year] )
)

PY Member by Gender =
VAR cYear =
MAX ( 'Table'[Year] )
RETURN
CALCULATE (
SUM ( 'Table'[Members] ),
FILTER ( ALLSELECTED ( 'Table' ), [Year] = cYear - 1 ),
VALUES ( 'Table'[Gender] )
)

Change of PY =
VAR cyMember =
CALCULATE (
SUM ( 'Table'[Members] ),
ALLSELECTED ( 'Table' ),
VALUES ( 'Table'[Year] ),
VALUES ( 'Table'[Gender] )
)
RETURN
cyMember - [PY Member by Gender]

Change of PY % =
DIVIDE ( [Change of PY], [PY Member by Gender] )
``````

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Community Support

Hi @cbiddle,

You can try to use the following measure formulas if they meet for your requirement:

``````Gender % by Year =
CALCULATE (
SUM ( 'Table'[Members] ),
ALLSELECTED ( 'Table' ),
VALUES ( 'Table'[Year] ),
VALUES ( 'Table'[Gender] )
)
/ CALCULATE (
SUM ( 'Table'[Members] ),
ALLSELECTED ( 'Table' ),
VALUES ( 'Table'[Year] )
)

PY Member by Gender =
VAR cYear =
MAX ( 'Table'[Year] )
RETURN
CALCULATE (
SUM ( 'Table'[Members] ),
FILTER ( ALLSELECTED ( 'Table' ), [Year] = cYear - 1 ),
VALUES ( 'Table'[Gender] )
)

Change of PY =
VAR cyMember =
CALCULATE (
SUM ( 'Table'[Members] ),
ALLSELECTED ( 'Table' ),
VALUES ( 'Table'[Year] ),
VALUES ( 'Table'[Gender] )
)
RETURN
cyMember - [PY Member by Gender]

Change of PY % =
DIVIDE ( [Change of PY], [PY Member by Gender] )
``````

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Announcements