How to count two columns against the same value...

[KRD]

Hello,

I'm looking for some help in how to count two columns against the same value. The data is regarding visits into and out of an area (for example, a shop, or a ward in a hospital), I have the data split into date/time columns and then the into hour and out of hour, how do I create a table that shows by hour of the day, how many people went into the area, and a second column to show those leaving the area. Example below: 

Hour of the day	Count into area	Count leaving area
0	2	6
1	3	8
2	4	0
3	0	0
4	6	0
5	0	2
6	0	1
7	5	3

Thank you in advance,

[mickey64]

Step 0: I use these data.

Step 1: I add a 'Date' column on Power Query Editor.

- Before -

- After -

Step 2: I add a  'Time' column on Power Query Editor.

- Before -

- After -

Step 3: I change the type of 'Time' column.

    'Time' column: 'Time' --> 'Decimal Number'

Step 4: I add a 'Hour' column below.

Step 5: I change the type of 'Hour' column.

    'Hour' column: 'Decimal Number' --> 'Whole Number'

Step 6: I click 'Close & Apply'.

Step 7: I make a matrix on Power BI Desktop.

[rajendraongole1]

Hi @KRD - Create a calculated table and in your dataset hope you have hour in and out columns. 

HourlySummary =
VAR IntoCounts =
SUMMARIZE (
'YourTableName',
'YourTableName'[Into Hour],
"Count Into Area", COUNTROWS('YourTableName')
)
VAR OutCounts =
SUMMARIZE (
'YourTableName',
'YourTableName'[Out Of Hour],
"Count Leaving Area", COUNTROWS('YourTableName')
)
RETURN
ADDCOLUMNS (
GENERATESERIES (0, 23, 1),
"Count Into Area",
COALESCE (LOOKUPVALUE (
IntoCounts[Count Into Area],
IntoCounts[Into Hour],
[Value]
), 0),
"Count Leaving Area",
COALESCE (LOOKUPVALUE (
OutCounts[Count Leaving Area],
OutCounts[Out Of Hour],
[Value]
), 0)
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!