cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Proportion and Percent Change by Year and Group

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 it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
Highlighted
Community Support
Community Support

Re: Proportion and Percent Change by Year and Group

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 it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors