How to perform a group by of a table then multiply the count by a measure?

[Anonymous]

I have a table that has data that looks like below

I also have a measure that is calculated from other things, for the purposes of this discussion lets say it equals 50. 

What i want to do is get the data to look like this.

 

In SQL i would very easily be able to do this with a query like below 

SELECT [COLUMN 1], COUNT(*) as [NEW COLUMN 2], (MEASURE*COUNT(*))

FROM TABLENAME

GROUP BY [COLUMN 1]

How can i do this in PowerBI?

[Anonymous · Accepted solution]

I ended up adding the datasource in a second time, editing on the way in, grouping by the department and having a count column.

I then added a calculated column to this subset that was the count column multipled by the measure from the other dataset.

Solved my problem as below.

[pxg08680]

@Anonymous

Out of my understanding you need each dept count and that count multiplied by 50.

From my ex:

I have taken D1,D2,D3,D4 as depts and found the no of times there are appeared and multiplied it by 50.

Step1:

Finding the count

Measure 2 = CALCULATE(COUNT(Table6[Dept]))

Step2:

Multiplying by 50 

Measure 3 = 50 * Table6[Measure 2]

As you have mentioned in your description that the measure value is 50, I have used the same.

Finally add the columns Department, Measure2 , Measure3 into Table visual.

Hope this gives you a solution.

Regards,

PXG08680

[Anonymous]

Close but doesnt seem to work. the Measure 2 = CALCULATE(COUNT(Table6[Dept])) counts all rows, and the Measure 3 = 50 * Table6[Measure 2] multiplies by the total amount.

When put into a table visual, the measure 3 doesnt break down per department, it shows the one number for all departments.

See below for what happened on Actual Data

Business Pillar = Department

Fields explained below (Cost per actioned alert is the measure thats calculated off something else and returns a number)

Business Pillar = Department

Incidents Per Pillar = Calculate(COUNT('LAST WEEK CRITICAL'[Business Pillar])) 

Cost Per Pillar = [Cost Per Actioned Alert]*[Incidents Per Pillar]