Forum Discussion
Category/Subcategory Calculation
I am very new to Dax and am guessing the solution is right under my nose, but I've exhausted my limited knowledge and need some guidance.
The data indicates number of people per category and with the Dax measures I calculate the average number of people per sub-category based on the number of sub-category occurences. The pivot table summarizes what I expect it to regarding the total number of people for each category and sub-category. The issue is that I would like the grand total to reflect the sum of the categories (in the case below 12+7.5+13+10).
SubCategory Count:=COUNT([SubCategory])
Total SubCategory Count:=calculate(count([SubCategory]),ALL([SubCategory]))
Category Manpower:=calculate(divide(sum([Manpower]),[SubCategory Count]),ALL([SubCategory]))
Manpower/SubCategory(Category):=divide([Subcategory Count],[Total SubCategory Count])*[Category MP]
Hi TL
Try this measure
Measure = IF(HASONEVALUE(Sheet7[category]),[Manpower/SubCategory(Category)],
SUMX(VALUES(Sheet7[category]),[Manpower/SubCategory(Category)]))Best Regards
Maggie
5 Replies
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).
Hi TL
Try this measure
Measure = IF(HASONEVALUE(Sheet7[category]),[Manpower/SubCategory(Category)],
SUMX(VALUES(Sheet7[category]),[Manpower/SubCategory(Category)]))Best Regards
Maggie
v-juanli-msftThanks Maggie! That did the trick. I'm now reviewing and reviewing again so I understand the logic used.
Hi team,
I think my problem might be quite simple, but related. I have to apply some calculations to incoming data. Whereby I am aim to have a percentage as the end result. I have 31 categories, and 131 subcategories, but there are differing numbers of subcategories per category. Per subcategory I need to check if there is a name listed, which would then result in an percentage.
I hope the below is illustrative. The percentages just need to be totals for the subcategory and not necessarily reflected on each row, but the formula/solution would need to be able to reflect incoming new data (which may be a copy of the dataset with new datestamps)
Major Category
Minor Category
Name
Expected
Actual
Percentage
A
A1
Dan
2
1
50%
A
A2
2
1
50%
B
B1
Dave
1
1
100%
C
C1
Carol
3
2
66%
C
C2
Ben
3
2
66%
C
C3
3
2
66%
D
D1
1
0
0%
