Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
KirkpM
Regular Visitor

Life events as a percentage of births

I am building a Power BI dashboard off an Excel document that mainly utilizes pivot tables. In a pivot table, I am able to create a calculation that identifies the proportion of all births that have a life event, i.e. what proportion of all the births for a specific time period (month of birth) had a pneumonia event?

The Pivot Table creates these values using the fields of EVENT and BirthDate (summarized as a birth month BMON). In the Pivot Table the column are assigned to EVENT, and the rows to BMON. The summation of values is the COUNT of ID (the unique individual reference number), with the Show Values As option set to "% OF", Base field = EVENT and Base item set as 1_BORN. The resulting values are then displayed as percentages. Of all the individuals born in the month of Feb 2019, 3.5% encountered a pneumonia event is an example result.

The data is structured with fields for ID, BDAT, BMON, EVENT, AGE, and EDAT (reference ID, birthdate, birth month, EVENT, age at event and event date). There may be multiple rows for each individual depending on the events they encountered.

In BI, I am unable to replicate this Excel calculation.

Thank you very much for any insights!

 

ID            BDAT             EVENT       AGE    EDAT             BMON

722817/29/20191_BORN07/29/20197/1/2019
722817/29/2019WEANED6910/6/20197/1/2019
11518811/16/20191_BORN011/16/201911/1/2019
1151913/9/20191_BORN03/9/20193/1/2019
1151913/9/2019PNEU.R1708/26/20193/1/2019
1151913/9/2019PNEU.R1738/29/20193/1/2019
1151923/7/20191_BORN03/7/20193/1/2019
1318182/1/2019PINKEYE1797/30/20192/1/2019
1318202/2/20191_BORN02/2/20192/1/2019
1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

@KirkpM =DIVIDE(DISTINCTCOUNT(Table5[ID]),CALCULATE(DISTINCTCOUNT(Table5[ID]),Table5[EVENT]="1_BORN"),0)

View solution in original post

6 REPLIES 6
wdx223_Daniel
Super User
Super User

@KirkpM =DIVIDE(DISTINCTCOUNT(Table5[ID]),CALCULATE(DISTINCTCOUNT(Table5[ID]),Table5[EVENT]="1_BORN"),0)

wdx223_Daniel:  Perfect!  That is exactly what I was looking for and really solved a big stumbling block.  Thank you!  KirkpM

Anonymous
Not applicable

// Careful: this measure honors all
// filters you might put on the data,
// so always make sure you understand
// what it returns. T is the table
// with the data you showed in your
// question. Thanks.

[Pneumonia %] =
var __pneumonia =
    CALCULATE(
        DISTINCTCOUNT( T[ID] ),
        KEEPFILTERS( T[EVENT] = "pneu.r" )
    )
var __born =
    DISTINCTCOUNT( T[ID] )
return
    DIVIDE( __pneumonia, __born )

daxer
Thanks very much for considering this question.  I had another request to provide clarrification.  Here's a further explanation.

 

An Excel pivot table delivers these counts of health events with a grouping focusing on the birth month.

KirkpM_6-1605719143234.png

 

I then use the Excel Pivot specifications mentioned in the original post (shown below) to determine the proportion affected (incidence rate) with the number born as the denominator. 

 

Here's the specifications for the Excel pivot summarization:

The Pivot Table creates the incidence values using the fields of EVENT and BirthDate (summarized as a birth month BMON). In the Pivot Table the column is assigned to EVENT, and the rows to BMON. The summation of values is the COUNT of ID (the unique individual reference number), with the Show Values As option set to "% OF", Base field = EVENT and Base item set as 1_BORN. The resulting values are then displayed as percentages. Of all the individuals born in the month of Feb 2019, 3.5% encountered a pneumonia event is an example result.

 

 

This is an example of the target output from an Excel Pivot Table

KirkpM_5-1605719053192.png


The categories of 1_BORN, SOLD and DIED are constants between different datasets, while the health conditions can be variable in name.

 

I hope this better explains the output goal. 

Thank you very much for considering this question! 

KirkpM

amitchandak
Super User
Super User

@KirkpM , expected output is not clear to mean, Can you highlight and explain

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Amitchandak
Thanks very much for your request for clarrification.

 

An Excel pivot table delivers these counts of health events with a grouping focusing on the birth month.

 

KirkpM_1-1605718422480.png

 

I then use the Excel Pivot specifications mentioned in the original post to determine the proportion affected (incidence rate) with the number born as the denominator. 

 

Here's the specifications for the Excel pivot summarization:

The Pivot Table creates the incidence values using the fields of EVENT and BirthDate (summarized as a birth month BMON). In the Pivot Table the column is assigned to EVENT, and the rows to BMON. The summation of values is the COUNT of ID (the unique individual reference number), with the Show Values As option set to "% OF", Base field = EVENT and Base item set as 1_BORN. The resulting values are then displayed as percentages. Of all the individuals born in the month of Feb 2019, 3.5% encountered a pneumonia event is an example result.

 

This is an example of the desired output

 

KirkpM_2-1605718485725.png


The categories of 1_BORN, SOLD and DIED are constants between different datasets, while the health conditions can be variable in name.

 

I hope this better explains the output goal. 

Thank you very much for considering this question! 

KirkpM

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors