How to create a table from the unique value pairs found in an existing table?

[sharon_net]

The data model being used is pasted above.

I am trying to create a table that contain unique pairs of Borough and incident_count from crime_report table.

I tried create a table of distinct borough values -

crimes_by_borough = DISTINCT(crime_report[Borough])

and then adding the incident_count

incident_count = VALUE(crime_report[incident_count], crimes_by_borough[Borough]=crime_report[Borough])

Could someone please help me with this?

[sharon_net]

 Hi,

my dataset is in the form of a CSV-

here is a snippet

LSOA Code,LSOA Name,Borough,Major Category,Minor Category,incident_count
E01000008,Barking and Dagenham 016A,E09000002,Arson and Criminal Damage,Criminal Damage,10
E01000009,Barking and Dagenham 016A,E09000002,Burglary,Domestic Burglary,7
E01000009,Barking and Dagenham 016A,E09000002,Drug Offences,Drug Trafficking,5

I'm expecting a table that sums up the incident counts per borough, since there could be multiple records with the same borough

Result expected is

Borough | incident_count

E000008| 10

E000009|12

[Anonymous · Accepted solution]

Hi @sharon_net ,

You can create a calculated table as below to get it:

Table =
SUMMARIZE (
    'crime_report',
    'crime_report'[LSOA Code],
    "incident_count", SUM ( 'crime_report'[incident_count] )
)

In addition,  you can create a Table visual with below Fields settings to get it directly...

Best Regards

[Ashish_Mathur]

Hi,

To a Table visual, drag Borough and write this measure

Total = sum(Data[incident_count])

Hope this helps.

[Ritaf1983]

Hi @sharon_net 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523