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

etc etc.

[edit] for clarification:

Basically, I have a category Fruit with an associated Fruit Measure, and I have created the following measures to get a summation of the multiple sweetness/tartness values for each fruit. I also take the percentage of column total (since the sweetness/tartness values are on a different scale and not inherently comparable). As an example: %ColTotal = bananaSweetness/TotalFruitSweetness => 170/(170 + 135 + 125 + 35) = .3656, as follows:

SumPerFruit =
SUMX ( VALUES ( Table[Fruit Measure] ), CALCULATE ( SUM ( Table[Value] ) ) )

PercentageofColTotal_SumPerFruit =
DIVIDE (
    [SumPerFruit],
    SUMX (
        VALUES ( Table[Fruit Measure] ),
        CALCULATE ( SUM ( Table[Value] ), ALLSELECTED ( table[Fruit] ) )
    )
)

This results with calculations like the following :

Fruit	Fruit Measure	SumPerFruit	PercentageofColTotal_SumPerFruit
banana	sweetness	170	.3656
banana	tartness	55	.1029
peach	sweetness	135	.2903
peach	tartness	75	.2206
apple	sweetness	125	.2688
apple	tartness	55	.1618
kiwi	sweetness	35	.0753
kiwi	tartness	175	.5147

[EDIT] for more clarification on SummaryFruitMeasure

Now, the above measures just combine the multiple sweetness/tartness values for each fruit into two values for every fruit-- one for tart, and one for sweet. Ideally, I just want to have one numerical average value that can summarize both the sweetness/tartness values for a fruit. Thus, I want to just take an average like the following:

BananaSummaryMeasure = (BananaTartness + BananaSweetness)/2 => (.3656 + .1029)/2

so I tried to get the average of PercentageofColtotal_SumPerFruit for every fruit as follows:

SummaryFruitMeasure =
SUMX (
    VALUES ( Table[Fruit] ),
    CALCULATE ( DIVIDE ( SUM ( Table[PercentageofColTotal_SumPerFruit] ), 2 ) )
)

with the intended result of:

Fruit	SummaryFruitMeasure
banana	.23425
peach	.255545
apple	.2153
kiwi	.295

However, I get an error with the SUM(Table[PercentageofColTotal_SumPerFruit]) section as follows:

Column PercentageofColTotal_SumPerFruit in table "Table" cannot be found or may not be used in this expression.

So I'm assuming that it can't be used in this expression because it's a measure. How do I get around this? Should I be using SUMMARIZECOLUMNS or something like that? I'm not sure if its possible to aggregate further on PercentageofColTotal_SumPerFruit?

Also, I have to use measures because the actual data would be filtered on.

[edit] I've also looked into something like this but that results in a table, and I want a measure at the end of this..

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

Answer 2

Your DAX is unnecessarily complicated and it's tripping you up. The particular error is that you cannot use SUM on anything except a table column (measures are not columns).

I'd recommend this instead:

SumValue = SUM ( Table[Value] )

PercentOfTotal =
    DIVIDE (
        [SumValue],
        CALCULATE ( [SumValue], ALLSELECTED ( Table ) )
    )

Using these two should work whether you break out into sweet/tart or not.

Answer 3

You can use summarize in measures:

SummaryFruitMeasure =
SUMX (
    SUMMARIZE(
       Table, 
       Table[Fruit],
       Table[Fruit Measure],
       "PercentageTotal", [PercentageofColTotal_SumPerFruit]
    ),
    DIVIDE ( SUM ( [PercentageTotal] ), 2 )
)