Add total number of values using a calculated column

[poweruser55]

In my data model I have columns that have the option Y and N. Say for columns A, B and C I want to count the collective number of Y and then divide it by the total of D +F . How can I do this with a calculated column?

[v-angzheng-msft]

Hi, @poweruser55 

Calculate formula Y/(D+F) based on one column or based on multiple columns (A,B,C)?

It makes it easier to give you a solution.

1. Sample (dummy dataset) data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Best Regards,
Community Support Team _ Zeon Zheng

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

[poweruser55]

calculate (A+B+C) they all contain either Y or N, so I just want to count Y from A, B and C and then divide it by columns (D+F) @v-angzheng-msft So the expected result  is (A+B+C)/(D+F) which needs to be retrieved by added the Y from column ABC and the total count of D and F

[v-angzheng-msft]

Hi, @poweruser55 

Try this:

Column = 
var _A=
    CALCULATE(
        COUNT('Table'[A]),
        ALL('Table'),'Table'[A]="Y")
var _B=
    CALCULATE(
        COUNT('Table'[B]),
        ALL('Table'),'Table'[B]="Y")
var _C=
    CALCULATE(
        COUNT('Table'[C]),
        ALL('Table'),'Table'[C]="Y")

var _D=
    CALCULATE(COUNT('Table'[D]),ALL('Table'))
var _F=
    CALCULATE(COUNT('Table'[F]),ALL('Table'))
var _RESULT=(_A+_B+_C)/(_D+_F)
RETURN 
    _RESULT

 Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[poweruser55]

@v-angzheng-msft This is being done in the query editor it is the same problem that someone else posted on this thread

[v-angzheng-msft]

Hi, @poweruser55 

Please create a calculated column from the menu below(1 or 2)

You can download my attachment for more.

Best Regards,
Community Support Team _ Zeon Zheng

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

[poweruser55]

When I remove the Var part it works fine but then that means I am going to have to create a bunch of columns and I don't want to do that, I want to keep it all in one function @v-angzheng-msft  Why is the var part causing issues?

[v-angzheng-msft · Accepted solution]

Hi, @poweruser55 

When you have VAR variables in your code, you need to use RETURN to return a result.

Column = 
var _A=
    CALCULATE(
        COUNT('Table'[A]),
        ALL('Table'),'Table'[A]="Y")
var _B=
    CALCULATE(
        COUNT('Table'[B]),
        ALL('Table'),'Table'[B]="Y")
var _C=
    CALCULATE(
        COUNT('Table'[C]),
        ALL('Table'),'Table'[C]="Y")

var _D=
    CALCULATE(COUNT('Table'[D]),ALL('Table'))
var _F=
    CALCULATE(COUNT('Table'[F]),ALL('Table'))
var _RESULT=(_A+_B+_C)/(_D+_F)
RETURN 
    _RESULT

Please refer to my previous code and attachments

.

How to Get Your Question Answered Quickly

Best Regards,
Community Support Team _ Zeon Zheng

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

[poweruser55]

Got it for that error but now I am getting a syntax error. 

I just did a test with one of the columns and can't figure out the syntax error. All the parenthesis look fine to me. @v-angzheng-msft 

[poweruser55]

Hi I am getting the error token Eof expected right after the first var @tackytechtom 

[tackytechtom]

Hi @poweruser55 ,

How about this:

TomsNewColumn =
VAR _countYA = CALCULATE ( COUNTROWS ( 'table' ), 'table'[Column] = "Y" )
VAR _countYB = CALCULATE ( COUNTROWS ( 'table' ), 'table'[Column] = "Y" )
VAR _countYC = CALCULATE ( COUNTROWS ( 'table' ), 'table'[Column] = "Y" )
VAR _SumDF = Table[D] + Table[F]

RETURN
DIVIDE ( _countYA + _countYB + _countYC, _SumDF  )

Does this help you? 🙂

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom

[poweruser55]

Why am I getting a syntax error? @tackytechtom  

[tackytechtom]

Can you show the whole formular that you used in a screenshot incl error message? 🙂

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom

[poweruser55]

@tackytechtom 

[tackytechtom]

Hi @poweruser55 ,

The code I used was DAX in PBI and not in power-query-m. 

Does it work for you to add the calculated column in PBI?

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom

[poweruser55]

I am getting an error of token eOF expected where countYA is @tackytechtom