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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
BassG
Frequent Visitor

Rate per 1000 using 2 Excel tables

Hi all.  I'm hoping someone can help me work out what I'm needing to do & that I'm posting this in the correct part of the forum.

 

I've created a Dashboard to analyse incidents at different establishments.  This can be filtered by Establishment name, Age group, the Year it happened, the Term (quarter) that it happened and/or Gender.
The spreadsheet that this comes from is a list of the individuals, each with a Reference number column & each of the fields above as columns.  The thing is, this data is only a list of thise involved in an incident & not a list of everyone at each establishment.

 

I've been asked to add a visual to show the rate per 1000, but I'm struggling to get this to work.
I've tried a couple of datafiles into excel & added them to PBI.

The first one that I tried has a list of every person at each establishment & has columns for ID number (which isn't the same as the reference number), Year, Term, Establishment, Age group & Gender.
For this one, the data is a full download for each Term of every Year (2019 to October 2023).
This has lots of data, so I thought it could filter the same way as teh other data.  But I had not a clue how to get this to work, so abandoned it.  The sheet is called "DataA"

 

The next lot of data is simplified.  It has columns for Establishment, Age group, Year & Number of people, so all the individuals for each Age group are counted rather than showing every individual.  Again, I thought I could get it to filter by the first 3 & show the rate per 1000, but couldn't figure it out at all (I know how to do these in Excel, but PBI & DAX/M is a different ballgame for me).

This sheet is called "DataB"

 

So I decided to simplify things even further & go with just Establishment, Number of people & Year.  So it simply has something like this for each row:

EstablishmentYearPeople
Place A2019142
Place B201998
Place C2019201
Place A2020136
Place B2020105
Place C2020199
Place A2021154
Place B202199
Place C2021213

I named it "DataC"

 

I then created a measure...

1 Count of Incident divided by People =
2 DIVIDE(COUNTA('Datafile'[Incident]), SUM('DataC'[People]))

But I'm struggling to get the *1000 part into the formula.
 
Can anyone help with this, or better still, help me work out how to do one of the ones with more filtering options, if it's at all possible?
 

Many thanks, G

1 ACCEPTED SOLUTION
chonchar
Helper V
Helper V

@BassG 

 

I don't know how to add the *1000 in the same one. 

 

But, you could create a new measure that looks like
New Measure =

(Count of incident dividedby people * 1000)

 

That is the only way I know how to solve that problem. Basically create a new measure and the first arguement is (old measure * 1000)

Best,

Cam

View solution in original post

2 REPLIES 2
chonchar
Helper V
Helper V

@BassG 

 

I don't know how to add the *1000 in the same one. 

 

But, you could create a new measure that looks like
New Measure =

(Count of incident dividedby people * 1000)

 

That is the only way I know how to solve that problem. Basically create a new measure and the first arguement is (old measure * 1000)

Best,

Cam

Thanks Cam 😊

I never thought of doing that.  It works a treat.

 

G

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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