Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Calculated Column Not Working

I have a calculated column named [Sales Amount Total] which is the SUM of two value columns [Presales] and [Vans].

 

I then created a new calculated column named [Binary] that converts any non zero value from [Sales Amount Total] to 1, else it shows 0.

Now I want to do a formula that takes the SUM of [Binary] and divides it by the COUNT of [Binary] but it won't let me do that.

The DAX syntax that seems logically correct to me to write is CALCULATE ( SUM [Binary] / COUNT [Binary] ). But it doesn't work and I'm not sure why.

I basically want a calculated column that shows percentage. So if 10 out of 20 stores had a sale, it will show me 10/20 or 50% in the grand total.

 

Need Help

1 Reply

  • Adescrit's avatar
    Adescrit
    Impactful Individual
    3 years ago

    Hi Anonymous 

     

    The following DIVIDE function should return the percentage calculation you're looking for:

    DIVIDE( SUM( Table[Binary]) , COUNT( Table[Binary] ) )


    Note I've made a couple of extra change to the formula here, besides changing CALCULATE to DIVIDE:

    1. Added the table name before [Binary]. Provided you have created a calculated column and not a measure you will need to add the table and column name in this format: Table[Binary]
    2. Added brackets (parentheses) around the column name, so SUM( Table[Binary] ) and COUNT( [Binary] )

    If this doesn't work, double-check that you have created a column rather than a measure. If it is a measure you have created, you can't COUNT or SUM a measure, you would need instead to create two separate measures that calculate the SUM and COUNT of the required column.