Value as percentage of another on multiple column lookup

[owenv]

I'm trying to calculate a percentage of vehicles involved in an incident based on the number of vehicles.

I have one table (Table A) with the values for total number of vehicles seen by type and month. My second table contains all the incidents with one row per incident (Table B). Table B also contains the type of vehicle involved and month of incident. Additionally, there is a type of incident column.

There are other columns in each table but I'm not interested in those for this purpose.

Here's an example:

Table A:

month	vehicle type	number of vehicles
April	vehicle type 3	10
April	vehicle type 2	23
August	vehicle type 3	48
August	vehicle type 1	13
December	vehicle type 1	33

Table B:

month	vehicle type	incident type
April	vehicle type 1	incident type 1
April	vehicle type 2	incident type 1
August	vehicle type 1	incident type 2
August	vehicle type 3	incident type 3
December	vehicle type 1	incident type 2
December	vehicle type 1	incident type 2

I'd like to make a third table based on a lookup of vehicly type and month, but also be able to carry across the incident type.

Desired result:

month	vehicle type	incident type	number of vehicles	number of incidents
April	vehicle type 3	null	10	null
April	vehicle type 2	incident type 1	23	1
August	vehicle type 1	incident type 2	13	1
August	vehicle type 3	incident type 3	48	1
December	vehicle type 1	incident type 2	33	2

If I'm able to get the desired result table shown above, I can caluclate the required % values.

[smpa01 · Accepted solution]

@owenv  you can write three measures like this

_incidentType =
CALCULATE (
    MAX ( 'Table B'[incident type] ),
    TREATAS (
        SUMMARIZE ( 'Table A', 'Table A'[month], 'Table A'[vehicle type] ),
        'Table B'[month],
        'Table B'[vehicle type]
    )
)


_numberOfIncidents =
CALCULATE (
    COUNT ( 'Table B'[incident type] ),
    TREATAS (
        SUMMARIZE ( 'Table A', 'Table A'[month], 'Table A'[vehicle type] ),
        'Table B'[month],
        'Table B'[vehicle type]
    )
)

pct =
VAR _deno =
    CALCULATE (
        COUNT ( 'Table B'[incident type] ),
        TREATAS (
            SUMMARIZE ( ALL ( 'Table A' ), 'Table A'[month], 'Table A'[vehicle type] ),
            'Table B'[month],
            'Table B'[vehicle type]
        )
    )
RETURN
    DIVIDE ( [_numberOfIncidents], _deno )

[owenv]

Thank you for the reply. This doesn't seem to return any values for me (I've updated all the variables). Also, the % needs to be represented as the number of vehicles for the corresponding row rather than the grand total. Apologies I wasn't clear on that.

[smpa01]

@owenv  what is the desired % based on the data that you provided?

you can simple change pct to this

[owenv]

number of incidents / number of vehicles*100

[smpa01]

@owenv  try this

[owenv]

Sorted! Thanks so much.

The reason I wasn't seeing the values as I mentioned earlier is because I missed a step. I need to create the third table using the groupby function and select only the columns I was interested in. Once I added your measures to that third table things started to work. Thanks so much, this is a huge help!

[smpa01]

@owenv  Glad to be of help. Can you please accept the answer !!!