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: cluster color value1 weights A blu 6,8 44 ...
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.

     

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.