Nested Aggregate function in Dax

[Vinod_G_245]

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

1. when category and any one of the below level(Subcategory,items) is selected then it is  average of sales( achieved).

2.When only category is selected(subcategory and items is not selected ) we want to take sum( montly average of column ). Im facing some challenges in this reqirement.

I used the below dax.

IF((AND(Sub-Category) = "",SELECTEDVALUE(Items) = "")),

SUM(Sales),AVERAGE(sales)  ---It is giving sum at week level but i want sum values at montly avergae value.

  expecting like this.

IF((AND(Sub-Category) = "",SELECTEDVALUE(Items) = "")),

SUM(average(Sales)),AVERAGE(sales) 

we tried  aggregate table but in modeling we are facing ambiguity, if this is sloved in Dax using variables it will be helpful for us.

Thanks in advance.

[lbendlin]

Please read about ISINSCOPE() and HASONEVALUE()

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Vinod_G_245]

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]

Hi Team /Ibendlin, 

if any additional information is required please ping me. 

Thanks

[lbendlin]

Here's the general idea

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