DAX measure to calculate count of average values in virtual table

[wini_R]

Hi Everyone,

I'd need to calculate the count of average values and find the mode as an ultimate goal. Let's consider this simple table as our source data:

deliv date	target
20220110	19
20220114	56
20220114	82
20220115	44
20220123	13
20220123	25
20220202	72
20220205	72
20220209	20
20220209	73
20220209	30
20220210	72
20220210	72
20220219	21
20220223	41
20220223	19
20220302	83
20220305	62
20220305	77
20220305	77

1. As a first step I need to group data by date and calculate the average of 'target'. I can achieve this by using SUMMARIZE function:

2. Then based on this virtual table I'd like to group by '@avg' column and get the count for each value. This should be the output:

When I have this table it should be fairly simple to find the mode which is 72 in this case.

The issue is that I cannot calculate the count based on the virtual table from step 1 ('_aggTab'). I tried to use the same logic with summarize but you cannot use count on virtual columns and I couldn't get countx to give correct result:

What would be the correct DAX count formula to get the output as in point 2?

[Anonymous]

Hi @wini_R ,

Based on your problems, here are my answers.

I create a table as you mentioned.

I write a new Measure that utilizes the COUNTX, SUMMARIZE and SUMX functions.

Measure =
SUMX (
    SUMMARIZE (
        'Table',
        'Table'[target],
        "Count", COUNTX ( 'Table', 'Table'[target] )
    ),
    [Count]
)

Finally, you will get what you want.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wini_R]

Thanks @Anonymous for the reply. However it seems that your solution is missing one step - instead of calculating the count based on aggregated average values (see step 1 in the initial post), it  calculates the count on raw data which gives different result. 

Do you think it would be possible to modify your solution according to the rquirement?

[Ahmedx]

pls try this

[wini_R]

Thanks @Ahmedx for this solution. It appears to work fine, however it adds some complexity to the model by introducing additional table and I'd prefer to avoid it for that relatively simple task. I'm sure this can be done using just the existing table.

[Ahmedx]

It won't work without an additional table

[wini_R · Accepted solution]

It's a very basic calculation, why would we need extra tables and complex logic to calculate it?

After some exploration I've foud 2 ways to calculate what I need:

1. using SUMMARIZE and COUNTX functions:

EVALUATE
VAR _tab1 =
ADDCOLUMNS(
    SUMMARIZE(tabB, tabB[deliv date])
	, "@avg", CALCULATE(AVERAGE(tabB[target]))
)
VAR _tab2 =
ADDCOLUMNS(
	SUMMARIZE(_tab1, [@avg]),
	"count of avg",
	COUNTX(
		FILTER(_tab1, [@avg] = EARLIER([@avg])),
		[@avg]
	)
)
RETURN _tab2

Output table:

2. using GROUPBY and COUNTX functions:

EVALUATE
VAR _tab1 =
GROUPBY(
	tabB,tabB[deliv date],
	"@avg", AVERAGEX(CURRENTGROUP(), tabB[target])
)
VAR _tab2 =
GROUPBY(
	_tab1, [@avg],
	"count of avg", COUNTX(CURRENTGROUP(), [@avg])
)
RETURN _tab2

The output is same as above.

Then if we need to calculate mode we can do this using i.e. MINX and either INDEX or TOPN by adding another variable to one of the expressions above:

MINX(
	INDEX(1, _tab2, ORDERBY([count of avg], DESC)),
	[@avg]
)