Proportion and Percent Change by Year and Group

[cbiddle]

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!

[v-shex-msft · Accepted solution]

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