cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cbiddle
Regular Visitor

Proportion and Percent Change by Year and Group

Hello, 

 

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

YearGenderMembers
2016F269391
2017F255061
2018F251992
2016M258512
2017M248718
2018M244728

 

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_MembersChange_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
v-shex-msft
Community Support
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] )

13.png

Regards,

Xiaoxin Sheng

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

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
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] )

13.png

Regards,

Xiaoxin Sheng

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

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors