Percentage of incident over a group

[Edds01388]

Hi can you help:

I have fivecoloumns I need to use to work out a percentage

Location, Date, Period, Incident Type, IP

Location - will have things like - escalators and stairs

Date - is the date of the incidnet

Period - is a value I have created to group the incidnets into our reporting period (e.g. Period 1-2023-24, Period 2-2023-24

Incident Type - accident, incident

IP - customer, staff

So what I am trying to do (easy in excel) - is to 

Count the number of escalator accidents in a period by "customer"

Count the numner of accidents by customer by the period

Then calculate the percentage of escalator accidents by customers for each period

[collinsg]

Good day Edds01388,

This type of calculation is ideally suited to being done in DAX once the data has been loaded into the semantic model. Let's assume you have loaded the data into a table in your semantic model. Let's say the table is called "tbl". You could create a measure,

Count of Rows = COUNTROWS('tbl')

For "Count the number of escalator accidents in a period by "customer" you could create a matrix visual containing "Period" in rows and "Count of Rows" as value and have the visual filtered for "escalator" and "accident".

For "Count the numner of accidents by customer by the period" you could create a matrix visual containing "Period" in rows and "customer" in columns with "Count of Rows" as value and have the visual filtered for "accident".

For "calculate the percentage of escalator accidents by customers for each period" you could create a matrix visual with "Period" on rows and filtered for "accident" and "customer" and create a percentage measure to use in values.

Percentage of Rows = DIVIDE( [Count of Rows], CALCULATE( [Count of Rows], REMOVEFILTERS(tbl[Period]) )

Hope this helps.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Edds01388]

Summary	Date	Incident Type	IP	Locus
Staff pushed over	07/01/2024	Crime/Assault	Staff	Shop
Staff fell over	16/08/2023	Accident	Local	Station
Customer fell back	17/04/2023	Incident	Public	Escalator
Man jumped over the rails	23/08/2023	Incident	Local	Shop
Cat ran onto the bus	01/07/2023	Crime/Assault	Local	Shop
Customer slipped	01/07/2023	Crime/Assault	Public	Escalator
Customer Fell over	13/05/2023	Accident	Public	Stairs

So in this data - there was 3 customer accidents of which 2 were escalator so around 66%

Here is some example data

[v-jingzhan-msft · Accepted solution]

Hi @Edds01388 

You can try these measures:

No. of accidents = COUNT('Table'[Date])

No. of Escalator = CALCULATE([No. of accidents],'Table'[Locus]="Escalator")

Percentage = DIVIDE([No. of Escalator], [No. of accidents])

They can be dynamically updated according to groups. 

Othewise, if you want a single measure for "Customer" group, you can use

Percentage of escalator accidents = DIVIDE(CALCULATE(COUNT('Table'[Summary]),'Table'[Column]="Customer",'Table'[Locus]="Escalator"),CALCULATE(COUNT('Table'[Summary]),'Table'[Column]="Customer"))

 

You can download the demo file to see details. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!