Forum Discussion
Category/Subcategory Calculation
Hi TL
Try this measure
Measure = IF(HASONEVALUE(Sheet7[category]),[Manpower/SubCategory(Category)],
SUMX(VALUES(Sheet7[category]),[Manpower/SubCategory(Category)]))Best Regards
Maggie
Hi TL
As tested, the grand total shows correctly, my test is as below.
dataset
| category | subcategory | manpower |
| A | A-1 | 3 |
| A | A-2 | 3 |
| A | A-2 | 3 |
| A | A-2 | 3 |
| B | B-1 | 1.25 |
| B | B-1 | 1.25 |
| B | B-2 | 1.25 |
| B | B-2 | 1.25 |
| B | B-2 | 1.25 |
| B | B-2 | 1.25 |
| C | C-1 | 2.6 |
| C | C-1 | 2.6 |
| C | C-2 | 2.6 |
| C | C-2 | 2.6 |
| C | C-2 | 2.6 |
| D | D-1 | 10 |
matrix
Could you show me how your dataset look like? just show an example instead of your all data.
Best Regards
Maggie
Hi Maggie,
To be a little more clear, the manpower number associated with each Subcategory entry is the total manpower number for the Category. So in your example the total number of manpower associated with Category A is 3. The measures for Category A divide that total into 4 parts and the pivot table reports the total for each Subcategory. In your example, the pivot should report for Category A a total of 3 and totals for Subcategories A-1 = 0.75 and A-2 = 2.25. The Category Grand Total should be 16.85 (3 + 1.25 + 2.6 +10).
