dax - Aggregate on measure in powerbi?

Question

So I have some measures created, with an example table such as:

Fruit	Fruit Measure	Value
banana	sweetness	90
banana	sweetness	80
banana	tartness	15
banana	tartness	20
peach	sweetness	70
peach	sweetness	65
peach	tartness	35
peach	tartness	40
apple	sweetness	65
apple	sweetness	60
apple	tartness	30
apple	tartness	25
kiwi	sweetness	20
kiwi	sweetness	15
kiwi	tartness	85
kiwi	tartness	90

question from:https://stackoverflow.com/questions/66052626/aggregate-on-measure-in-powerbi

Answer 1

There can be 2 approaches, depending on the granularity of your data. They can be visualised like this. The first measure (Measure one) always shows your desired result, regardless, if you put Fruit Measure in the visual or not. The second one does not and allows you to see it at the fruit level, as well as at the measurement level.

Measure one =
CALCULATE (
    DIVIDE (
        SUMX (
            VALUES ( Fruit[Fruit Measure] ),
            DIVIDE (
                CALCULATE ( SUM ( Fruit[Value] ) ),
                CALCULATE ( SUM ( Fruit[Value] ), ALLSELECTED ( Fruit[Fruit] ) )
            )
        ),
        COUNTROWS ( VALUES ( Fruit[Fruit Measure] ) )
    ),
    ALLSELECTED ( Fruit[Fruit Measure] )
)

---

Measure two =
DIVIDE (
    SUMX (
        VALUES ( Fruit[Fruit Measure] ),
        DIVIDE (
            CALCULATE ( SUM ( Fruit[Value] ) ),
            CALCULATE ( SUM ( Fruit[Value] ), ALLSELECTED ( Fruit[Fruit] ) )
        )
    ),
    COUNTROWS ( VALUES ( Fruit[Fruit Measure] ) )
)

I decided to use COUNTROWS() (instead of simple division by 2) , to make it work with any number of Fruit Measure values.

In the second measure, VALUES ( Fruit[Fruit Measure] ) could be put in a variable, as it's used twice, to make it look less repetitive.

My recommendation would be to go with something like Measure two, as it's more flexible (and quite elegant), but it depends on you reporting requirements.

EDIT

Actually, Measure two is unnecessarily convoluted. It should be simplified like this:

Measure three =
AVERAGEX (
    VALUES ( Fruit[Fruit Measure] ),
    DIVIDE (
        CALCULATE ( SUM ( Fruit[Value] ) ),
        CALCULATE ( SUM ( Fruit[Value] ), ALLSELECTED ( Fruit[Fruit] ) )
    )
)

Apologies for that.

And just to answer your question about SUM(Table[PercentageofColTotal_SumPerFruit]) giving an error, you can use measures in SUMX, e.g. SUMX(Table, [PercentageofColTotal_SumPerFruit]). Note, I'm not refering to correctness of the result of this operation in this context.

EDIT2

Here is another take on Measure one based on the comment. This time it calculates values based on all fruit, regardless of slicer selection / filtering.

Measure one = 
CALCULATE (
    AVERAGEX (
        VALUES ( Fruit[Fruit Measure] ),
        DIVIDE (
            CALCULATE ( SUM ( Fruit[Value] ) ),
            CALCULATE ( SUM ( Fruit[Value] ), ALL ( Fruit[Fruit] ) )
        )
    ),
    ALL ( Fruit[Fruit Measure] )
)