topic Re: Measure that sums up a column based on the average value grouped by category in DAX Commands and Tips

Measure that sums up a column based on the average value grouped by category

Anonymous — Fri, 12 Aug 2022 23:07:34 GMT

Pretty straight forward. Any help is appreciated. Example below:

Category	Value
a	10
a	10
b	20
b	20

Measure's results: 30

Because a + b = 10 + 20 = 30

Re: Measure that sums up a column based on the average value grouped by category

tamerj1 — Fri, 12 Aug 2022 23:32:11 GMT

Hi Anonymous

you can use

SUMX (

SUMMARIZE (

TableName,

TableName[Category],

"@Average", AVERAGE ( TableName[Value] )

[@Average]
)

Re: Measure that sums up a column based on the average value grouped by category

Anonymous — Thu, 18 Aug 2022 18:41:54 GMT

One more question: How would I convert this into a COUNTX?

This worked beautifully. Thanks so much by the way.

Re: Measure that sums up a column based on the average value grouped by category

tamerj1 — Thu, 18 Aug 2022 19:02:37 GMT

Hi Anonymous

what do you want to count exactly?

Re: Measure that sums up a column based on the average value grouped by category

Anonymous — Thu, 18 Aug 2022 19:04:28 GMT

I want to count the averages grouped by category

Re: Measure that sums up a column based on the average value grouped by category

tamerj1 — Thu, 18 Aug 2022 19:09:02 GMT

Anonymous

The number of averages would be the number of categories. what is different is the distinct number of averages. In this case you may try

= COUNTROWS ( DISTINCT ( SELECTCOLUMNS ( SUMMARIZE ( TableName, TableName[Category], "@Average", AVERAGE ( TableName[Value] ) ), "@@Average", [@Average] ) ) )

Re: Measure that sums up a column based on the average value grouped by category

Anonymous — Thu, 18 Aug 2022 19:53:06 GMT

I would think that would work and I have come up with something similar. What I really need is the count of categories that change over a time period (see below). There would only be a count of 1 for the below snippet for 2022.26 column.

And then display them as a bar chart with x-axis being yyyy.ww

Re: Measure that sums up a column based on the average value grouped by category

tamerj1 — Thu, 18 Aug 2022 20:06:30 GMT

sorry but still not able to pick up the idea.

Re: Measure that sums up a column based on the average value grouped by category

Anonymous — Thu, 18 Aug 2022 20:41:56 GMT

the associated output of the measure would be this:

Category	2022.24	2022.25	2022.26
BCP-06	0	0	1
BCP-08	0	0	0

Counts if the category changes over time. The first screen shot from the previous reply is simply a count of the category.