Forum Discussion

Vinod_G_245's avatar
Vinod_G_245
Helper I
3 years ago

Nested Aggregate function in Dax

Hi Team, we have 3 categories ( Category, Subcategory, items) in table which we will recieve weekely, we will roll up to monthly, yearly. we have two requirements in report( montly level slicer). ...
Need Help
Vinod_G_245's avatar
Vinod_G_245
Helper I
3 years ago

Hi Ibendlin, 

Thanks for reply,

im attaching  sample data  drive link with requirement.

 

Requirement:

we have Month slicer.
IF
BRAND_NAME, BRAND_TYPE, SUB BRAND is selected in slicer then it has to give average of that month.
example:
for Jan Month
Amul Brand, ASA Brand_type, Sub_Brand
ASA-Required_Headcount-16
ASA-Actual_Headcount- 15.2
else
only BRAND_NAME is selected in slicer , BRAND_TYPE, SUB_BRAND is not selected then it has to give sum(average of that month) of that particular Brand_Name .
example:
for Jan Month
BARND=AMUL, Brand_Type is NA , Sub_Brand is NA
AMUL-Required_Headcount-1908
AMUL-Actual_Headcount- 2055

Drive Link: 

https://drive.google.com/drive/folders/12jBQszuRNVknUCcVZCNbpdDxelwKl1-D?usp=sharing 

Brand Brand_type Req_HC montly Avg Actual_HC montly Avg
AMUL ASA 16 15
AMUL ASP 42 42
AMUL DO 183 187
AMUL DPR 100 107
AMUL DTM 78 115
AMUL MDM 399 406
AMUL NAH 172 194
AMUL NAOF 0 0
AMUL PRH 19 33
AMUL PRT 557 566
AMUL SSMS 170 196
AMUL SSMS AH 172 194
Monthly sum 1908 2055

  • Vinod_G_245's avatar
    Vinod_G_245
    Helper I
    3 years ago

    Hi Team /Ibendlin, 

     

    if any additional information is required please ping me. 

     

    Thanks

    • lbendlin's avatar
      lbendlin
      Super User
      3 years ago

      Here's the general idea

       

       

      You can figure out why the numbers don't match.