# Help. Count all numbers >= 1 as 1

L

#### Legacy 301750

##### Guest
I'm creating a Dax formula where when I count the rows if the number is 1 or greater than one I get a return values of 1 and 0 is 0.

example

 [Net Activations Count](sum) [Test Activation Count] (count) 1 1 2 1 4 1 0 0

<tbody>
</tbody>

The first column column counts the number records. The "Test Activation Count" counts all records on the Source column but will only return a 1 or 0.

The problem I'm getting is that the functions that I'm generating are a loop and I can't brake the loop.

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Welcome to the Board!

The problem I'm getting is that the functions that I'm generating are a loop and I can't brake the loop.
I am not sure I understand. Where exactly is this loop you are talking about?

Welcome to the forum. We will always try to help.

What is the formula you are using? If the values in A are integers 0 and up, then:

B2= "=sign(A2)"

and then you can sum column B

or you could use Countif()

=COUNTIF(A2:A30,">0")

Here is a sample function I have tried. hopefully this clears some

[Test Activation Count]=IF([Net Activation Count]=1,1,IF([Net Activation Count]>=2,1,0))

The column [Net Activation Count] is as rows can will have the any value starting on 0 and up. The [Test Activation Count] is Account of the previous column but only returning 0 or 1

--------

I can't use COUNTIF is a DAX (power pivot) function.

 nums 5 0 23 0 Count of nums 0 nums Total 2 0 4 4 2 1 6 4 2 8 5 2 10 6 1 0 8 1 5 10 1 4 23 1 Grand Total 13 grand total minus number under total is what you need =offset(\$f\$1,match("total",f2:f1000,0) +1 ,0) finds the 4 =offset(\$e\$1,match("Grand total",e2:e1000,0),1 findsthe 13

<colgroup><col span="4"><col><col><col span="6"></colgroup><tbody>
</tbody>

I found kind of a hack to make it work.

=IFERROR(CALCULATE(SUM[Net Activations Count]/SUM[Net Activation Count])),0)

This does what I want. If you guys have any other suggesting, I'm all ears.

The formula above worked but The only problem is that it will not total in the columns or rosin the power pivot table. Any suggestions? It seams to be applying the same formula to do the totals instead of summing up the row value or column value.

EX: the formula that is driving this is:
[Test Activation Count]=IFERROR(CALCULATE(SUM[Net Activations Count]/SUM[Net Activation Count])),0)

The result I'm getting is:
 row sample [Test Activation Count]2 [Test Activation Count]3 [Test Activation Count]4 Total a 1 1 0 1 b 1 1 1 1 c 0 0 1 1 total 1 1 1 1

<tbody>
</tbody>

And I should be getting
 row sample [Test Activation Count]2 [Test Activation Count]3 [Test Activation Count]4 Total a 1 1 0 2 b 1 1 1 3 c 0 0 1 1 total 2 2 2 6

<tbody>
</tbody>

lets start again - col A is a list of numbers from zero to 99999
col B is 1 if number is not zero, zero if it is zero
let us say you have 17 "ones" and 4 zeroes

what do you want to do with that information

I'm creating mockups for some dashboards. Right now we are using data that is coming from an external excel file and the data model resides inside the excel document. Eventually all the data and calculated fields will move to the SQL server.

The purpose of this mockup is to give a presage of activations of a product in a period of time. The activations are done by store. In our Data, we have a fact that tells us how many products have been activated. This is by single product.

This Data looks like this:
fact: Sum of Net Activation Count

 Net Activation Count Store Name Product 1 Product 2 Product 3 Product 5 Total store1 1 3 1 2 7 store2 0 2 1 0 3 store3 2 1 0 3 6 total 3 6 2 5 16

<tbody>
</tbody>

The next step is to figure out the if the product has been activated. So 0 of no and 1 if any products as been activated.

We are using this formula to get those numbers: =if([Sum of Net Activation Count] >0,1,0)

Once we use this formulate we get:
I'm calling this calculated field [Test Net Activation Count]
The results for this should be
 Test Net Activation Count Store Name Product 1 Product 2 Product 3 Product 5 Total store1 1 1 1 1 4 store2 0 1 1 0 2 store3 1 1 0 1 3 total 2 3 2 2 9

<tbody>
</tbody>

The problem we are getting is that for the pivot table column we care getting a 1 instead of the sum of the values. This is the result:
 Test Net Activation Count Store Name Product 1 Product 2 Product 3 Product 5 Total store1 1 1 1 1 1 store2 0 1 1 0 1 store3 1 1 0 1 1 total 1 1 1 1 1

<tbody>
</tbody>

The total is not summing the columns and instead using the same formula to get the total.

At the the end what we need is a the percentage of products activated. EX

 Test Net Activation Count Store Name Product 1 Product 2 Product 3 Product 7 Total store1 1 1 1 1 100% store2 0 1 1 0 75% store3 1 1 0 1 75%

<tbody>
</tbody>

Hope this makes sense

too complicated, I do not understand - do it a bite at a time - answer my question - and what is meant by a product is activated....

Replies
1
Views
147
Replies
9
Views
462
Replies
9
Views
248
Replies
19
Views
355
Replies
12
Views
262

1,219,917
Messages
6,150,946
Members
450,996
Latest member
darko1515s

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back