Forum Discussion
How to count two columns against the same value...
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 th...
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!!