Categorizing fields in multiple buckets

[brizy]

I'm trying to create new columns that better reflect total attendance counts.

Currently, the following statuses exist:
- "RSVP"

- "Attended"
- "Non RSVP"

If a user RSVP's to an event their status will be "RSVP"

Then, if they attend the event, this will be updated to "Attended"

-if they don't attend, it will remain as "RSVP"

If they did not RSVP to the event but attended, their status will reflect as "Non RSVP"

I want to have the following categories:
- ActualAttendance: (Attended + Non RSVP)
- RSVPAttended: (Attended)
- NonRSVPAttended: (Non RSVP)

- TotalRSVP: (Attended + RSVP)
- Did not attend: (RSVP)

[Anonymous · Accepted solution]

Hi  @brizy ,

I created some data:

Here are the steps you can follow：

1. Create calculated column.

Count =
var _count=
COUNTX(FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))&&MONTH('Table'[Date])=MONTH(EARLIER('Table'[Date]))&&'Table'[statuses]=EARLIER('Table'[statuses])),[statuses])
var _max=CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),YEAR(EARLIER('Table'[Date]))&&MONTH('Table'[Date])=MONTH(EARLIER('Table'[Date]))&&'Table'[statuses]=EARLIER('Table'[statuses])))
return
IF(
    'Table'[Date]=_max,_count,0)

2. Create calculated table.

True =
var _summ1=
SUMMARIZE('Table',
'Table'[Date],
"statuses","ActualAttendance",
"Count",
SUMX(FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))&&MONTH('Table'[Date])=MONTH(EARLIER('Table'[Date]))&&'Table'[statuses] in { "Attended","Non RSVP"}),[Count]))

var _summ11=
UNION('Table',_summ1)

var _summ2=
SUMMARIZE('Table',
'Table'[Date],
"statuses","RSVPAttended",
"Count",
SUMX(FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))&&MONTH('Table'[Date])=MONTH(EARLIER('Table'[Date]))&&'Table'[statuses] ="Attended"),[Count]))

var _summ22=
UNION(_summ11,_summ2)

var _summ3=
SUMMARIZE('Table',
'Table'[Date],
"statuses","NonRSVPAttended",
"Count",
SUMX(FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))&&MONTH('Table'[Date])=MONTH(EARLIER('Table'[Date]))&&'Table'[statuses] ="Non RSVP"),[Count]))

var _summ33=
UNION(_summ22,_summ3)

var _summ4=
SUMMARIZE('Table',
'Table'[Date],
"statuses","TotalRSVP",
"Count",
SUMX(FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))&&MONTH('Table'[Date])=MONTH(EARLIER('Table'[Date]))&&'Table'[statuses] in { "Attended","RSVP"}),[Count]))

var _summ44=
UNION( _summ33,_summ4)

var _summ5=
SUMMARIZE('Table',
'Table'[Date],
"statuses","Did not attend",
"Count",
SUMX(FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))&&MONTH('Table'[Date])=MONTH(EARLIER('Table'[Date]))&&'Table'[statuses] ="RSVP"),[Count]))

var _summ55=
UNION(_summ44,_summ5)

return
_summ55

Create computed column in True table

COUNT_ALL =
IF(
    'True'[Date]=
CALCULATE(MIN('True'[Date]),FILTER(ALL('True'),YEAR('True'[Date])= YEAR(EARLIER('True'[Date]))&&MONTH('True'[Date])=MONTH(EARLIER('True'[Date]))&&'True'[statuses]=EARLIER('True'[statuses]))),
[Count],BLANK())

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

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