Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Issue with SUM outside filters

I'm trying to replicate a business calculation that exists in my environment on PowerBI, but I'm not getting what I expect as a result. I have 2 tables that I made as an example and replicate the re...
Need Help
  • ChenwuZhu_Gmail's avatar
    ChenwuZhu_Gmail
    4 years ago

    Hi Anonymous ,

     

    Is this right?

    FinalValue =
VAR _s =
    ADDCOLUMNS (
        CROSSJOIN ( VALUES ( 'Calendar'[Month] ), 'Weight' ),
        "final", [Weight] * [NPSCalculated]
    )
RETURN
    SUMX ( _s, [final] )

     

    Result:

     

    Best Regards

     

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

     

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi ChenwuZhu_Gmail,

Well, it is partially right. While ussing the table with the weight on it the result is the expected as you shown.

The thing is: I'm actually not planning to use the complete table, just the Month and the Final Value and put then in a line graph. So the results should consider the respective weight when it is not being used as a category.

The previous solution is ok for 2021-12, but is wrong for 2021-11 when not using the weight on the table:

On 2021-11 the final value should be 0,33. Is it possible we get these results while in this conditions v-chenwuz-msft ?


 

ChenwuZhu_Gmail's avatar
ChenwuZhu_Gmail
Icon for Resolver I rankResolver I
4 years ago

Hi Anonymous ,

 

Is this right?

FinalValue =
VAR _s =
    ADDCOLUMNS (
        CROSSJOIN ( VALUES ( 'Calendar'[Month] ), 'Weight' ),
        "final", [Weight] * [NPSCalculated]
    )
RETURN
    SUMX ( _s, [final] )

 

Result:

 

Best Regards

 

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

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    ChenwuZhu_Gmail thanks a lot for this reply, it really solves the case showed and helped in my case. I will have to open a new one due to my issue having another specific cross between tables, but you already helped, thanks for the effort!