sunnyBNH013
New Member
- Joined
- Nov 30, 2022
- Messages
- 1
- Office Version
- 365
- Platform
- Windows
Hello all! First time poster here and I'm struggling to find a solution to my problem:
Ideally, what I'd like to be able to achieve is automation of these calculations using Power Pivot, if possible. Here in my pivot table, I have Restaurants with the IDs of individuals along with the occurrences (how many times one purchased from said restaurant). The calculations I'm wanting to automate are 1. a Distinct Count (using the formula
) for each row of restaurants and 2. a Prevalence calculation, which takes the Distinct Count from said row divided by the total count.
Is there any way to automate this using Calculated Fields/etc in Power Pivot? Appreciate your willingness to help!
Ideally, what I'd like to be able to achieve is automation of these calculations using Power Pivot, if possible. Here in my pivot table, I have Restaurants with the IDs of individuals along with the occurrences (how many times one purchased from said restaurant). The calculations I'm wanting to automate are 1. a Distinct Count (using the formula
Excel Formula:
COUNTIF(N4:R4,">0")
Is there any way to automate this using Calculated Fields/etc in Power Pivot? Appreciate your willingness to help!
SamplePivotTable_30NOV.xlsx | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | |||
1 | ID | Restaurant | ||||||||||||
2 | 1 | McDonalds | ||||||||||||
3 | 1 | Burger King | ||||||||||||
4 | 1 | McDonalds | ||||||||||||
5 | 1 | Burger King | Total Count = 31 | |||||||||||
6 | 1 | Chick-fil-A | Count of Restaurant | Column Labels | Distinct Count- "=COUNTIF(N4:R4,">0")" | Prevalence Calculation- Distinct Count Divided by Total Count | ||||||||
7 | 1 | McDonalds | Row Labels | 1 | 2 | 3 | 4 | 5 | Grand Total | 4 | 13% | |||
8 | 2 | Chick-fil-A | Brusters | 1 | 6 | 1 | 8 | 3 | ||||||
9 | 2 | Taco Bell | Burger King | 2 | 5 | 1 | 8 | 2 | ||||||
10 | 2 | Taco Bell | Chick-fil-A | 1 | 1 | 4 | 6 | 3 | ||||||
11 | 2 | Taco Bell | Freddy's | 1 | 1 | 2 | 1 | |||||||
12 | 3 | Brusters | McDonalds | 3 | 3 | 1 | ||||||||
13 | 3 | Freddy's | Taco Bell | 3 | 1 | 4 | ||||||||
14 | 4 | Chick-fil-A | Grand Total | 7 | 15 | 2 | 4 | 3 | 31 | |||||
15 | 4 | Chick-fil-A | ||||||||||||
16 | 4 | Chick-fil-A | ||||||||||||
17 | 4 | Chick-fil-A | ||||||||||||
18 | 1 | Brusters | ||||||||||||
19 | 2 | Brusters | ||||||||||||
20 | 5 | Freddy's | ||||||||||||
21 | 2 | Burger King | ||||||||||||
22 | 2 | Burger King | ||||||||||||
23 | 2 | Burger King | ||||||||||||
24 | 2 | Burger King | ||||||||||||
25 | 2 | Burger King | ||||||||||||
26 | 5 | Burger King | ||||||||||||
27 | 5 | Taco Bell | ||||||||||||
28 | 2 | Brusters | ||||||||||||
29 | 2 | Brusters | ||||||||||||
30 | 2 | Brusters | ||||||||||||
31 | 2 | Brusters | ||||||||||||
32 | 2 | Brusters | ||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
L7 | L7 | =K7/31 |
K7:K12 | K7 | =COUNTIF(D7:H7,">0") |