Forum Discussion

TL's avatar
TL
Regular Visitor
7 years ago
Solved

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...
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi TL

    Try this measure

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

     

    Best Regards

    Maggie

dancarr's avatar
dancarr
Frequent Visitor
7 years ago

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%