Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculation with different slice from the same table

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:

clustercolorvalue1weights
Ablu6,844
Agreen7,810
Dblu7,922
Bgreen8,234
Cgreen8,423
Cblu6,822
Bblu8,423
Agreen7,811

 

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

Need Help
Tutorial Requests
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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.

     

3 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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. 

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    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.