Nested Aggregate function in Dax

Question

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&nbsp; 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)&nbsp; ---It is giving sum at week level but i want sum values at montly avergae value.&nbsp; expecting like this.IF((AND(Sub-Category) = "",SELECTEDVALUE(Items) = "")),SUM(average(Sales)),AVERAGE(sales)&nbsp;&nbsp;we tried&nbsp; aggregate table but in modeling we are facing ambiguity, if this is sloved in Dax using variables it will be helpful for us.&nbsp;Thanks in advance.&nbsp;

lbendlin · Answer

Here's the general idea

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

lbendlin · Answer

Please read about ISINSCOPE() and HASONEVALUE()
&nbsp;
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-p/963216 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
&nbsp;
&nbsp;

vinod_g_245 · Answer

Hi Ibendlin,&nbsp;Thanks for reply,im attaching&nbsp; sample data&nbsp; drive link with requirement.&nbsp;Requirement:we have Month slicer.IFBRAND_NAME, BRAND_TYPE, SUB BRAND is selected in slicer then it has to give average of that month.example:for Jan MonthAmul Brand, ASA Brand_type, Sub_BrandASA-Required_Headcount-16ASA-Actual_Headcount- 15.2elseonly 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 MonthBARND=AMUL, Brand_Type is NA , Sub_Brand is NAAMUL-Required_Headcount-1908AMUL-Actual_Headcount- 2055Drive Link:&nbsp;https://drive.google.com/drive/folders/12jBQszuRNVknUCcVZCNbpdDxelwKl1-D?usp=sharing&nbsp;Brand Brand_type Req_HC montly Avg Actual_HC montly AvgAMUL ASA 16 15AMUL ASP 42 42AMUL DO 183 187AMUL DPR 100 107AMUL DTM 78 115AMUL MDM 399 406AMUL NAH 172 194AMUL NAOF 0 0AMUL PRH 19 33AMUL PRT 557 566AMUL SSMS 170 196AMUL SSMS AH 172 194Monthly sum 1908 2055

vinod_g_245 · Answer

Hi Team /Ibendlin,&nbsp;&nbsp;if any additional information is required please ping me.&nbsp;&nbsp;Thanks

Forum Discussion

Nested Aggregate function in Dax

4 Replies

Related content

Nested IF

Boolean Aggregate Functions?

How to nest If in Functions

Aggregating nested tables

performing filter and aggregate function on dataset before applying TOPN