Category/Subcategory Calculation

[TL]

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]

                   

[dancarr]

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%

[v-juanli-msft · Accepted solution]

Hi @TL

Try this measure

Measure = IF(HASONEVALUE(Sheet7[category]),[Manpower/SubCategory(Category)],
             SUMX(VALUES(Sheet7[category]),[Manpower/SubCategory(Category)]))

Best Regards

Maggie

[TL]

@v-juanli-msftThanks Maggie! That did the trick.  I'm now reviewing and reviewing again so I understand the logic used.

[v-juanli-msft]

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

[TL]

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).