How to sum and average at same column with different aggregation levels

[Arashi]

Hello,

I hope you can help me with this. I'm sure it is quite simple but I don't seem to find the right measure.

I need the create a column in my matrix that creates the sum up of the number of products by store level on year 2022. However when the matrix is grouped by type of store level, it should show the average of the number of products.

I'm using something like:

IF (ISFILTERED (Table [Type of Store] , CALCULATE (AVERAGE (Table [#Products], Table [Year] = "2022") ,

CALCULATE (SUM (Table [#Products], Table [Year] = "2022"))

But this is not working at all, the result is not even a correct average of the number of products.

Expected result shoul be:

Find below the dataset you can use to replicate the situation:

Store	Type of Store	Category	#Products	Year
11	B	Category1	0	2021
11	B	Category1	1860	2022
11	B	Category2	0	2021
11	B	Category2	0	2022
11	B	Category3	0	2021
11	B	Category3	838	2022
11	B	Category4	0	2021
11	B	Category4	0	2022
25	B	Category2	658	2021
25	B	Category2	1319	2022
25	B	Category3	484	2021
25	B	Category3	7	2022
25	B	Category4	430	2021
25	B	Category4	503	2022
25	B	Category5	915	2021
25	B	Category5	0	2022
65	D	Category2	315	2021
65	D	Category2	346	2022
65	D	Category4	778	2021
65	D	Category4	220	2022
65	D	Category5	28	2021
65	D	Category5	0	2022
112	D	Category1	181	2021
112	D	Category1	366	2022
112	D	Category2	942	2021
112	D	Category2	0	2022
112	D	Category3	614	2021
112	D	Category3	225	2022
112	D	Category4	171	2021
112	D	Category4	0	2022
2052	C	Category2	0	2021
2052	C	Category2	489	2022
2052	C	Category3	0	2021
2052	C	Category3	196	2022
2052	C	Category4	0	2021
2052	C	Category4	313	2022
2052	C	Category5	0	2021
2052	C	Category5	217	2022
2067	C	Category2	44	2021
2067	C	Category2	545	2022
2067	C	Category3	278	2021
2067	C	Category3	136	2022
2067	C	Category4	772	2021
2067	C	Category4	386	2022
2067	C	Category5	186	2021
2067	C	Category5	174	2022
3254	E	Category2	910	2021
3254	E	Category2	0	2022
3254	E	Category3	498	2021
3254	E	Category3	0	2022
3254	E	Category4	282	2021
3254	E	Category4	0	2022
3254	E	Category5	120	2021
3254	E	Category5	0	2022
4534	E	Category2	41	2021
4534	E	Category2	0	2022
4534	E	Category3	875	2021
4534	E	Category3	0	2022
4534	E	Category4	587	2021
4534	E	Category4	0	2022
4534	E	Category5	541	2021
4534	E	Category5	0	2022
6756	A	Category1	730	2021
6756	A	Category1	1717	2022
6756	A	Category2	247	2021
6756	A	Category2	313	2022
6756	A	Category3	699	2021
6756	A	Category3	911	2022
6756	A	Category4	134	2021
6756	A	Category4	639	2022
7866	C	Category2	737	2021
7866	C	Category2	699	2022
7866	C	Category3	951	2021
7866	C	Category3	132	2022
7866	C	Category4	82	2021
7866	C	Category4	338	2022
7866	C	Category5	580	2021
7866	C	Category5	226	2022

[Anonymous · Accepted solution]

HI @Arashi,

You can take a look at the following blog about how to use Dax expression to check the current row context levels.

You can use if statements with these filters to check hierarchy level and write corresponding expressions to replace that level calculation:

Clever Hierarchy Handling in DAX - SQLBI

Regards,

Xiaoxin Sheng

[daXtreme]

To do such calculations you have to use ISINSCOPE to figure out which level you're working with and then perform the correct aggregation. Have a look at this vid ISINSCOPE - DAX Guide - YouTube.