Lets start with giving more background.
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
