Calculation with different slice from the same table

[Anonymous]

Hi everyone, 

I am new to PowerBI and I am doing a project for my master. My issue is the following:

I have a table like the following:

cluster	color	value1	weights
A	blu	6,8	44
A	green	7,8	10
D	blu	7,9	22
B	green	8,2	34
C	green	8,4	23
C	blu	6,8	22
B	blu	8,4	23
A	green	7,8	11

The idea is to select one of the 4 clusters (i.e. A) as a filter and have a measure that computes the weighted average of value1 with the weights of the rest of the clusters (B C D).

I've tried several things but I am not even sure if something like this is possible. 

The formula that I want to apply is: 

SUM(table[value1 (filtered for cluster A)] * table[weights (notA)]) / SUM[weights(notA)]

Thank you in advance 🙂 

edit: wrong first term in sum

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your descriptin, in my understanding, if A is selected in the filter, your expected result is (6.8*44+7.8*10+7.8*11) / (22+34+23+22+23). Here is my solution.

Create a measure.

Measure =
VAR _select =
    SUMX (
        FILTER ( ALL ( 'myTable' ), 'myTable'[cluster] IN VALUES ( myTable[cluster] ) ),
        'myTable'[value1] * 'myTable'[weights]
    )
VAR _unselect =
    SUMX (
        FILTER (
            ALL ( 'myTable' ),
            NOT ( 'myTable'[cluster] IN VALUES ( myTable[cluster] ) )
        ),
        'myTable'[weights]
    )
RETURN
    DIVIDE ( _select, _unselect )

Get the result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[selimovd]

Hey @Anonymous ,

it would be easier with a real file or tables. But the following approach should do it:

Weighted Average =
VAR vSelectedClusters = VALUES ( myTable[cluster] )
VAR vWeightNotCluster =
    CALCULATE (
        SUM ( myTable[weights] ),
        ALL ( myTable ),
        NOT ( myTable[weights] ) IN vSelectedClusters
    )
RETURN
    ( SUM ( myTable[weights] ) * vWeightNotCluster ) / vWeightNotCluster

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic
 

[Anonymous]

Hi, thanks for your reply! 

I tried this answer and it says there's an error:

Error Message:
MdxScript(Model) (47, 45) Calculation error in measure 'overall_pivot_table'[Weighted Average]: 
The function 'CONTAINSROW' does not support the comparison between values of type Text and type Integer.
Try to use the functions VALUE or FORMAT to convert one of the values.

I translated from italian.

I edit my post with the right formula.