cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ISUGraber Member
Member

Create Histogram using Quartiles

Hi Everyone,

I am looking at creating a histogram that will show the average tenure of employees by quartiles. 

 

I used the below code to try and get quartiles however I can't seem to use this in a bar chart to mimic a histogram.  So basically I would like the quartiels across the bottom of the histogram/bar chart as the X axis and then count of employees that fall into each bucket. 

 

The other issue I noticed with the below code is I seem to have to pull in Tenure into the table in order for the below calculation to show the quartiles.  I would like a user to be able to select different locations and then get the quartiles for that location. 

 

I also want to apply this to employee wages as well but am using tenure as a test.

 

 

Quartile Avg Tenure = 
VAR FirstQ =
    PERCENTILEX.INC (
        ALL ( 'DimEmployees'[Tenure] ),
        'DimEmployees'[Tenure],
        .25
    )
VAR SecondQ =
    PERCENTILEX.INC (
        ALL ( 'DimEmployees'[Tenure] ),
        'DimEmployees'[Tenure],
        .50
    )
VAR ThirdQ =
    PERCENTILEX.INC (
        ALL ( 'DimEmployees'[Tenure] ),
        'DimEmployees'[Tenure],
        .75
    )
VAR ThisVal =
    SELECTEDVALUE ( 'DimEmployees'[Tenure] )
RETURN
    IF (
        ThisVal <= FirstQ,
        1,
        IF (
            ThisVal > FirstQ
                && ThisVal <= SecondQ,
            2,
            IF ( ThisVal > SecondQ && ThisVal <= ThirdQ, 3, 4 )
        )
    )

 

 

2 REPLIES 2
Community Support Team
Community Support Team

Re: Create Histogram using Quartiles

Hi @ISUGraber ,

Can you please share a dummy file? If you can't, please share some sample data and your expected results that will make us understand clearly about your requirement.

 

Please see this post regarding How to Get Your Question Answered Quickly: 

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ISUGraber Member
Member

Re: Create Histogram using Quartiles

@v-xuding-msft , how is this?  and thanks for the reply.

 

sample data:

 

Employee ID            Tenure      Location

abc                               .4           A

def                              1.2           B

g                                  .5            B

h                                  5              A

i                                    4.2            B

 

Expected Results:

These are assigned quartiles dynamically which I can then put into a histogram.  So above another column of Quartiles/percentiles would be added but it can change depending on what location(s) you choose.  The histogram can be a bar chart but would look somethign like below.  Where the X axis are the quartiles and the Y axis is the employee count.

 

Does this help?

 

histogram.png

 

 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 24 members 1,559 guests
Please welcome our newest community members: