noob question, sum by status

[Anonymous]

I have this activity table with unique lines for each status on a course for a customer:

CustomerID Course         Status              Type

0001            Course1       Signed up        E-learning

0002            Course2       Signed up        Classroom

0003            Course2       Not signed up Classroom

0004            Course2       Signed up        Classroom

0005            Course1       Not signed up E-learning

And I am trying to get this overview table:

Course        Type              Sum signed up        Sum not signed up

Course1      E-learning     1                              1

Course2      Classroom     2                              1

I am quite new to PowerBI and trying to find out how to add a measure that gives me a table like that. Any help will be very appreciated

[amitchandak · Accepted solution]

@Anonymous , Option one.

In matrix visual  Course and Type on row. Status on column and 

measure = countrows(Table) on  values

of use two measures

sum Signed up = countrows(filter( Table, Table[Status]= "Signed up"))

sum Not Signed up = countrows(filter( Table, Table[Status]= "Not Signed up"))

[Samarth_18 · Accepted solution]

Hi @Anonymous 

you can create two measure like below:-

Sum not signed up = CALCULATE(COUNT('Table'[Status ]),'Table'[Status ] = "Not signed up")

Sum signed up = CALCULATE(COUNT('Table'[Status ]),'Table'[Status ] = "Signed up")

Output:-

Thanks,

Samarth

[Anonymous]

Thanks, all! I might use a mix of your suggested solutions 🙂

[Pragati11]

HI @Anonymous ,

Please mark the helpful suggestions as solutions to this thread as these may help others on the forum as well. 🙂

Thanks,

Pragati

[Pragati11 · Accepted solution]

Hi @Anonymous,

You can achieve this in Power Query Editor in Power BI Desktop.

This is thhe data that you shared:

Right Click on Customer ID column and select Remove. You will get:

Right-click on Course column and Duplicate it. You will get:

Now just drag Course-copy column to the left and bring it just before Type column. See below:

Just select Course-copy and Type columns (Ctrl+click on Windows machine) --> Right click --> Merge

Once you merge both column you will get:

Now select Status column and under Transform on top ribbon select pivot column option. You will get a new window:

Just click OK here and you will get the following:

Now we are just 1step away from the desired output.

Select Merged column and on top ribbon Split by delimiter:

A new window opens. Just make sure to have following options and then click ok:

You will get following:

Now just rename the highlighted columns (right-click on column and choose rename) and you will get your desired results:

Thanks,

Pragati

[Samarth_18 · Accepted solution]

Hi @Anonymous 

you can create two measure like below:-

Sum not signed up = CALCULATE(COUNT('Table'[Status ]),'Table'[Status ] = "Not signed up")

Sum signed up = CALCULATE(COUNT('Table'[Status ]),'Table'[Status ] = "Signed up")

Output:-

Thanks,

Samarth

[amitchandak · Accepted solution]

@Anonymous , Option one.

In matrix visual  Course and Type on row. Status on column and 

measure = countrows(Table) on  values

of use two measures

sum Signed up = countrows(filter( Table, Table[Status]= "Signed up"))

sum Not Signed up = countrows(filter( Table, Table[Status]= "Not Signed up"))