Hello my MS Excel expert friends,
I have been trying to create Pivot Tables from a dataset and to show the percentage difference in rates charged, between the company and its competition.
I have added the data to the data model and used a few simple Dax Functions - Percentiles (See Mini worksheet).
Is there a way to show the rates difference using DAX (In %) between "Acme Corporation" Versus "All other Companies" for each column?
Please see the manual method I would use to get this, in yellow.
Pivot Table:
Dataset:
I have been trying to create Pivot Tables from a dataset and to show the percentage difference in rates charged, between the company and its competition.
I have added the data to the data model and used a few simple Dax Functions - Percentiles (See Mini worksheet).
Is there a way to show the rates difference using DAX (In %) between "Acme Corporation" Versus "All other Companies" for each column?
Please see the manual method I would use to get this, in yellow.
Pivot Table:
| Clients Analysis 11.19.2022 Pivot Tables.xlsx | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 5 | 22% | |||||||||||
| 6 | Title | Associate | =(28187-23175)/23175 | |||||||||
| 7 | ||||||||||||
| 8 | Year | Graded Title | Groupings | Count of Title | Min of Service Rate | Average of Service Rate | Max of Service Rate | 25TH Percentile | 50TH Percentile | 75TH Percentile | ||
| 9 | 2022 | Junior Associate | Acme Corporation | 5 | $28,187 | $39,325 | $54,040 | $29,526 | $41,106 | $43,765 | ||
| 10 | All Other Companies | 31 | $23,175 | $42,445 | $59,175 | $35,775 | $42,075 | $53,550 | ||||
| 11 | Junior Associate Total | 36 | $23,175 | $42,012 | $59,175 | $35,325 | $42,075 | $53,550 | ||||
| 12 | Midlevel Associate | Acme Corporation | 9 | $30,131 | $47,048 | $56,427 | $44,699 | $47,447 | $55,502 | |||
| 13 | All Other Companies | 1 | $38,700 | $38,700 | $38,700 | $38,700 | $38,700 | $38,700 | ||||
| 14 | Midlevel Associate Total | 10 | $30,131 | $46,213 | $56,427 | $40,704 | $46,104 | $53,864 | ||||
| 15 | Senior Associate | Acme Corporation | 14 | $27,591 | $43,255 | $54,993 | $37,284 | $41,383 | $50,602 | |||
| 16 | All Other Companies | 18 | $41,625 | $55,463 | $79,875 | $42,075 | $53,550 | $62,550 | ||||
| 17 | Senior Associate Total | 32 | $27,591 | $50,122 | $79,875 | $41,622 | $48,163 | $55,476 | ||||
| 18 | 2022 Total | 78 | $23,175 | $45,878 | $79,875 | $38,793 | $42,075 | $53,550 | ||||
Pivot Table | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E5 | E5 | =(28187-23175)/23175 |
Dataset:
| Clients Analysis 11.19.2022 Pivot Tables.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Service Rate | Graded Title | Title | Type | Firm | Year | Groupings | ||
| 2 | $33,975 | Junior Associate | Associate | Clients A | Ace Store | 2022 | All Other Companies | ||
| 3 | $27,225 | Junior Associate | Associate | Clients A | Ace Store | 2022 | All Other Companies | ||
| 4 | $31,950 | Junior Associate | Associate | Clients A | Ace Store | 2022 | All Other Companies | ||
| 5 | $31,950 | Junior Associate | Associate | Clients A | Ace Store | 2022 | All Other Companies | ||
| 6 | $30,150 | Junior Associate | Associate | Clients A | Ace Store | 2022 | All Other Companies | ||
| 7 | $27,225 | Junior Associate | Associate | Clients A | Ace Store | 2022 | All Other Companies | ||
| 8 | $38,700 | Midlevel Associate | Associate | Clients A | Ace Store | 2022 | All Other Companies | ||
| 9 | $34,875 | Accountant | Accountant | Clients B | Ace Store | 2022 | All Other Companies | ||
| 10 | $39,600 | Accountant | Accountant | Clients B | Ace Store | 2022 | All Other Companies | ||
| 11 | $41,625 | Marketing Director | Marketing Director | Clients B | Ace Store | 2022 | All Other Companies | ||
| 12 | $41,625 | Marketing Director | Marketing Director | Clients B | Ace Store | 2022 | All Other Companies | ||
| 13 | $59,175 | Marketing Director | Marketing Director | Clients B | Ace Store | 2022 | All Other Companies | ||
| 14 | $51,075 | Marketing Director | Marketing Director | Clients B | Ace Store | 2022 | All Other Companies | ||
| 15 | $43,425 | Marketing Director | Marketing Director | Clients B | Ace Store | 2022 | All Other Companies | ||
| 16 | $41,625 | Marketing Director | Marketing Director | Clients B | Ace Store | 2022 | All Other Companies | ||
| 17 | $50,850 | Marketing Director | Marketing Director | Clients B | Ace Store | 2022 | All Other Companies | ||
| 18 | $42,300 | Marketing Director | Marketing Director | Clients B | Ace Store | 2022 | All Other Companies | ||
| 19 | $41,625 | Marketing Director | Marketing Director | Clients B | Ace Store | 2022 | All Other Companies | ||
| 20 | $49,725 | Marketing Director | Marketing Director | Clients A | Acme Corporation | 2022 | Acme Corporation | ||
| 21 | $37,800 | Marketing Director | Marketing Director | Clients A | Acme Corporation | 2022 | Acme Corporation | ||
| 22 | $47,025 | Marketing Director | Marketing Director | Clients A | Acme Corporation | 2022 | Acme Corporation | ||
| 23 | $48,825 | Marketing Director | Marketing Director | Clients A | Acme Corporation | 2022 | Acme Corporation | ||
| 24 | $37,800 | Marketing Director | Marketing Director | Clients A | Acme Corporation | 2022 | Acme Corporation | ||
| 25 | $58,500 | Marketing Director | Marketing Director | Clients A | Acme Corporation | 2022 | Acme Corporation | ||
| 26 | $58,500 | Marketing Director | Marketing Director | Clients A | Acme Corporation | 2022 | Acme Corporation | ||
| 27 | $45,225 | Marketing Director | Marketing Director | Clients A | Acme Corporation | 2022 | Acme Corporation | ||
| 28 | $49,275 | Marketing Director | Marketing Director | Clients A | Acme Corporation | 2022 | Acme Corporation | ||
| 29 | $55,125 | Marketing Director | Marketing Director | Clients A | Acme Corporation | 2022 | Acme Corporation | ||
| 30 | $37,800 | Marketing Director | Marketing Director | Clients A | Acme Corporation | 2022 | Acme Corporation | ||
| 31 | $53,775 | Marketing Director | Marketing Director | Clients A | Acme Corporation | 2022 | Acme Corporation | ||
| 32 | $45,225 | Marketing Director | Marketing Director | Clients A | Acme Corporation | 2022 | Acme Corporation | ||
| 33 | $43,425 | Marketing Director | Marketing Director | Clients A | Acme Corporation | 2022 | Acme Corporation | ||
| 34 | $51,075 | Marketing Director | Marketing Director | Clients A | Acme Corporation | 2022 | Acme Corporation | ||
| 35 | $51,075 | Marketing Director | Marketing Director | Clients B | Acme Corporation | 2022 | Acme Corporation | ||
| 36 | $59,850 | Marketing Director | Marketing Director | Clients B | Acme Corporation | 2022 | Acme Corporation | ||
| 37 | $55,125 | Marketing Director | Marketing Director | Clients B | Acme Corporation | 2022 | Acme Corporation | ||
| 38 | $18,900 | Cost Accountant | Cost Accountant | Clients B | Acme Corporation | 2022 | Acme Corporation | ||
| 39 | $18,900 | Cost Accountant | Cost Accountant | Clients B | Acme Corporation | 2022 | Acme Corporation | ||
| 40 | $21,375 | Cost Accountant | Cost Accountant | Clients B | Acme Corporation | 2022 | Acme Corporation | ||
| 41 | $15,975 | Cost Accountant | Cost Accountant | Clients B | Acme Corporation | 2022 | Acme Corporation | ||
| 42 | $11,925 | Cost Accountant | Cost Accountant | Clients B | Acme Corporation | 2022 | Acme Corporation | ||
| 43 | $63,000 | Midlevel Director | Director | Clients B | Acme Corporation | 2022 | Acme Corporation | ||
| 44 | $63,000 | Midlevel Director | Director | Clients B | Acme Corporation | 2022 | Acme Corporation | ||
| 45 | $61,425 | Senior Director | Director | Clients B | Acme Corporation | 2022 | Acme Corporation | ||
| 46 | $41,106 | Junior Associate | Associate | Clients A | Acme Corporation | 2022 | Acme Corporation | ||
| 47 | $28,187 | Junior Associate | Associate | Clients A | Acme Corporation | 2022 | Acme Corporation | ||
| 48 | $43,765 | Junior Associate | Associate | Clients A | Acme Corporation | 2022 | Acme Corporation | ||
| 49 | $54,040 | Junior Associate | Associate | Clients A | Acme Corporation | 2022 | Acme Corporation | ||
| 50 | $29,526 | Junior Associate | Associate | Clients A | Acme Corporation | 2022 | Acme Corporation | ||
| 51 | $29,426 | Junior Director | Director | Clients A | Acme Corporation | 2022 | Acme Corporation | ||
| 52 | $31,660 | Junior Director | Director | Clients A | Acme Corporation | 2022 | Acme Corporation | ||
| 53 | $30,905 | Junior Director | Director | Clients A | Acme Corporation | 2022 | Acme Corporation | ||
| 54 | $57,028 | Junior Director | Director | Clients A | Acme Corporation | 2022 | Acme Corporation | ||
| 55 | $32,943 | Junior Director | Director | Clients B | Acme Corporation | 2022 | Acme Corporation | ||
| 56 | $30,843 | Junior Director | Director | Clients B | Acme Corporation | 2022 | Acme Corporation | ||
| 57 | $50,634 | Junior Director | Director | Clients B | Acme Corporation | 2022 | Acme Corporation | ||
| 58 | $39,372 | Midlevel Associate | Associate | Clients B | Acme Corporation | 2022 | Acme Corporation | ||
| 59 | $30,131 | Midlevel Associate | Associate | Clients B | Acme Corporation | 2022 | Acme Corporation | ||
| 60 | $44,699 | Midlevel Associate | Associate | Clients B | Acme Corporation | 2022 | Acme Corporation | ||
| 61 | $55,502 | Midlevel Associate | Associate | Clients B | Acme Corporation | 2022 | Acme Corporation | ||
| 62 | $44,760 | Midlevel Associate | Associate | Clients B | Acme Corporation | 2022 | Acme Corporation | ||
| 63 | $47,447 | Midlevel Associate | Associate | Clients B | Acme Corporation | 2022 | Acme Corporation | ||
| 64 | $56,144 | Midlevel Associate | Associate | Clients A | Acme Corporation | 2022 | Acme Corporation | ||
| 65 | $48,949 | Midlevel Associate | Associate | Clients A | Acme Corporation | 2022 | Acme Corporation | ||
| 66 | $56,427 | Midlevel Associate | Associate | Clients A | Acme Corporation | 2022 | Acme Corporation | ||
| 67 | $35,646 | Senior Associate | Associate | Clients A | Acme Corporation | 2022 | Acme Corporation | ||
| 68 | $39,071 | Senior Associate | Associate | Clients A | Acme Corporation | 2022 | Acme Corporation | ||
| 69 | $41,152 | Senior Associate | Associate | Clients A | Acme Corporation | 2022 | Acme Corporation | ||
| 70 | $27,591 | Senior Associate | Associate | Clients A | Acme Corporation | 2022 | Acme Corporation | ||
| 71 | $41,614 | Senior Associate | Associate | Clients A | Acme Corporation | 2022 | Acme Corporation | ||
| 72 | $39,776 | Senior Associate | Associate | Clients A | Acme Corporation | 2022 | Acme Corporation | ||
| 73 | $36,688 | Senior Associate | Associate | Clients B | Acme Corporation | 2022 | Acme Corporation | ||
| 74 | $34,646 | Senior Associate | Associate | Clients B | Acme Corporation | 2022 | Acme Corporation | ||
| 75 | $53,937 | Senior Associate | Associate | Clients B | Acme Corporation | 2022 | Acme Corporation | ||
| 76 | $47,187 | Senior Associate | Associate | Clients B | Acme Corporation | 2022 | Acme Corporation | ||
| 77 | $49,139 | Senior Associate | Associate | Clients B | Acme Corporation | 2022 | Acme Corporation | ||
| 78 | $51,090 | Senior Associate | Associate | Clients B | Acme Corporation | 2022 | Acme Corporation | ||
| 79 | $53,042 | Senior Associate | Associate | Clients B | Acme Corporation | 2022 | Acme Corporation | ||
| 80 | $54,993 | Senior Associate | Associate | Clients B | Acme Corporation | 2022 | Acme Corporation | ||
| 81 | $79,875 | Senior Director | Director | Clients B | Cupcake LLC | 2022 | All Other Companies | ||
| 82 | $63,000 | Senior Director | Director | Clients A | Cupcake LLC | 2022 | All Other Companies | ||
| 83 | $70,425 | Senior Director | Director | Clients A | Cupcake LLC | 2022 | All Other Companies | ||
| 84 | $56,925 | Senior Director | Director | Clients A | Cupcake LLC | 2022 | All Other Companies | ||
| 85 | $79,875 | Senior Director | Director | Clients A | Cupcake LLC | 2022 | All Other Companies | ||
| 86 | $79,875 | Senior Director | Director | Clients A | Cupcake LLC | 2022 | All Other Companies | ||
| 87 | $79,875 | Senior Director | Director | Clients A | Cupcake LLC | 2022 | All Other Companies | ||
| 88 | $68,625 | Senior Director | Director | Clients A | Cupcake LLC | 2022 | All Other Companies | ||
| 89 | $63,675 | Senior Director | Director | Clients A | Cupcake LLC | 2022 | All Other Companies | ||
| 90 | $79,875 | Senior Director | Director | Clients A | Cupcake LLC | 2022 | All Other Companies | ||
| 91 | $65,250 | Senior Director | Director | Clients B | Cupcake LLC | 2022 | All Other Companies | ||
| 92 | $79,875 | Senior Director | Director | Clients B | Cupcake LLC | 2022 | All Other Companies | ||
| 93 | $53,550 | Junior Associate | Associate | Clients B | Cupcake LLC | 2022 | All Other Companies | ||
| 94 | $53,550 | Junior Associate | Associate | Clients B | Cupcake LLC | 2022 | All Other Companies | ||
| 95 | $53,550 | Junior Associate | Associate | Clients B | Cupcake LLC | 2022 | All Other Companies | ||
| 96 | $42,075 | Junior Associate | Associate | Clients B | Cupcake LLC | 2022 | All Other Companies | ||
| 97 | $42,075 | Junior Associate | Associate | Clients B | Cupcake LLC | 2022 | All Other Companies | ||
| 98 | $53,550 | Junior Associate | Associate | Clients B | Cupcake LLC | 2022 | All Other Companies | ||
| 99 | $42,075 | Junior Associate | Associate | Clients B | Cupcake LLC | 2022 | All Other Companies | ||
| 100 | $53,550 | Junior Associate | Associate | Clients B | Cupcake LLC | 2022 | All Other Companies | ||
| 101 | $53,550 | Junior Associate | Associate | Clients A | Éclair Inc | 2022 | All Other Companies | ||
| 102 | $53,550 | Junior Associate | Associate | Clients A | Éclair Inc | 2022 | All Other Companies | ||
| 103 | $53,550 | Junior Associate | Associate | Clients A | Éclair Inc | 2022 | All Other Companies | ||
| 104 | $42,075 | Junior Associate | Associate | Clients A | Éclair Inc | 2022 | All Other Companies | ||
| 105 | $35,775 | Junior Associate | Associate | Clients A | Éclair Inc | 2022 | All Other Companies | ||
| 106 | $42,075 | Junior Associate | Associate | Clients A | Éclair Inc | 2022 | All Other Companies | ||
| 107 | $53,550 | Junior Associate | Associate | Clients A | Éclair Inc | 2022 | All Other Companies | ||
| 108 | $42,075 | Junior Associate | Associate | Clients A | Éclair Inc | 2022 | All Other Companies | ||
| 109 | $42,075 | Junior Associate | Associate | Clients A | Éclair Inc | 2022 | All Other Companies | ||
| 110 | $35,775 | Junior Associate | Associate | Clients A | Éclair Inc | 2022 | All Other Companies | ||
| 111 | $35,775 | Junior Associate | Associate | Clients A | Éclair Inc | 2022 | All Other Companies | ||
| 112 | $53,550 | Junior Associate | Associate | Clients A | Éclair Inc | 2022 | All Other Companies | ||
| 113 | $53,550 | Junior Associate | Associate | Clients A | Éclair Inc | 2022 | All Other Companies | ||
| 114 | $42,075 | Junior Associate | Associate | Clients A | Éclair Inc | 2022 | All Other Companies | ||
| 115 | $42,075 | Junior Associate | Associate | Clients A | Éclair Inc | 2022 | All Other Companies | ||
| 116 | $57,600 | Junior Associate | Associate | Clients B | Éclair Inc | 2022 | All Other Companies | ||
| 117 | $42,075 | Junior Associate | Associate | Clients B | Éclair Inc | 2022 | All Other Companies | ||
| 118 | $53,550 | Junior Associate | Associate | Clients B | Éclair Inc | 2022 | All Other Companies | ||
| 119 | $23,175 | Junior Associate | Associate | Clients B | Éclair Inc | 2022 | All Other Companies | ||
| 120 | $35,775 | Junior Associate | Associate | Clients B | Éclair Inc | 2022 | All Other Companies | ||
| 121 | $59,175 | Junior Associate | Associate | Clients B | Éclair Inc | 2022 | All Other Companies | ||
| 122 | $42,075 | Junior Associate | Associate | Clients B | Éclair Inc | 2022 | All Other Companies | ||
| 123 | $35,775 | Junior Associate | Associate | Clients B | Éclair Inc | 2022 | All Other Companies | ||
Data | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| G2:G123 | G2 | =IF([@Firm]="Acme Corporation ","Acme Corporation ","All Other Companies") |
Last edited:
