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

[Anonymous]

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

[Anonymous]

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.

[tamerj1 · Accepted solution]

Hi @Anonymous 

you can use

SUMX (

SUMMARIZE (

TableName, 

TableName[Category],

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

),

[@Average]
)

[Anonymous]

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

This worked beautifully. Thanks so much by the way.

[tamerj1]

Hi @Anonymous 

what do you want to count exactly?

[Anonymous]

I want to count the averages grouped by category

[tamerj1]

@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]
        )
    )
)

[Anonymous]

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

[tamerj1]

sorry but still not able to pick up the idea.