Sum one column and then divide against the count of another column

[Anonymous]

Hello, I have a column "Open" that lists the number of open positions and a column "Name" that I would like to have a total count of. The math formula would look like this. Open/Name*100=__%. I've set it up to work in Excel, but not sure how to enter it in power query since there are multiple calculations. Is it best to build from a blank query or advanced editor? Current status: Table.RowCount(Query 1) Any help would be appreciated. Thank you!

[camargos88 · Accepted solution]

Hi @Anonymous ,

You can use this code:

[Open] / List.Count(#"Changed Type"[Name])

This DAX solution would be better to handle it:

Measure = DIVIDE(SUM('Table'[Open]), CALCULATE(DISTINCTCOUNT('Table'[Name]), ALL('Table')))

Measure 2 =

VAR _qtdeName = CALCULATE(DISTINCTCOUNT('Table'[Name]), ALL('Table'))

VAR _qtOpenPosition = SUM('Table'[Open])

RETURN DIVIDE(_qtdeName + _qtOpenPosition - _qtOpenPosition, _qtOpenPosition + _qtdeName)

[camargos88]

Hi @Anonymous,

Try this one:

let
_open = [Open],
_name = [Name]
in
_open / List.Sum(Table.SelectRows(#"Changed Type", each [Name] = _name)[Open]) * 100

The bold part is your step name before this new column.

[Anonymous]

Ricardo, 

Thank you. The synax worked, but it produced an error in the cell stating that it can't find column [Open]. I merged it as a query from another source, so the top is listed as [1.2 Open]. Would that change the formula?

Thank you!

Derek

[camargos88]

@Anonymous ,

Can you post some example of your dataset ?

[Anonymous]

Here you go. Ultimately I'll need two different final values. If I can just get one to work I want to continue learning and figure out the other one. Thank you!

% Open against Total Names (Open/(Name)*100 = __%

and 

% Open against Total Name and Open (Open/(Name+Open))*100 = __%

 

[camargos88]

@Anonymous ,

Can you explain the logic with an example using this data, please ?

[Anonymous]

There are (7) names, let's call them Employees and there are (3) Open positions. So out of a total of (10) positions, the company is 70% staffed. 

Open / (Active+Open) = % Staffed 

Does that help?

[camargos88 · Accepted solution]

Hi @Anonymous ,

You can use this code:

[Open] / List.Count(#"Changed Type"[Name])

This DAX solution would be better to handle it:

Measure = DIVIDE(SUM('Table'[Open]), CALCULATE(DISTINCTCOUNT('Table'[Name]), ALL('Table')))

Measure 2 =

VAR _qtdeName = CALCULATE(DISTINCTCOUNT('Table'[Name]), ALL('Table'))

VAR _qtOpenPosition = SUM('Table'[Open])

RETURN DIVIDE(_qtdeName + _qtOpenPosition - _qtOpenPosition, _qtOpenPosition + _qtdeName)

[Anonymous]

Ricardo, 

I changed the table name and here is what I'm getting back. I've played around with it a few different ways, but have been unable to get rid of the error. Any thoughts? 

[camargos88 · Accepted solution]

@Anonymous ,

You are inputing a DAX language in Power Query.

Try importing the table and try it creating measure.

Take a look at this file: Download PBIX 

This example only has DAX code, let me know if you still need to do it using Power Query.