Issue with SUM outside filters

[Renan-rdo]

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 real life problem.

Table Sales:

dateSold      Sector    Idsale  NPS
01/12/2021    A         1       good
01/11/2021    A         2       bad
01/11/2021    A         3       good
01/11/2021    B         4       bad
01/10/2021    A         5       bad
01/10/2021    B         6       good
01/10/2021    B         7       good
01/09/2021    A         8       good
01/09/2021    A         9       good
01/09/2021    A         10      good

And Weights table:

SECTOR  Weight
A       0,7
B       0,3

I created some fields to do my math and they do what I expect, but the last field doesnt. I did a COUNT for GOOD and BAD and total values on the last 3 months based on field NPS using this method:

QuantityGOOD = 
CALCULATE(
    COUNTA(Sales[Idsale])
    , DATESINPERIOD(Sales[dateSold], SELECTEDVALUE(Sales[dateSold]), -3, MONTH)
    , Sales[NPS] == "good"
)

Then Created a measure with the math using my fields:

NPSCalculated = 
[QuantityGOOD] / [QuantityTotal] - [QuantityBad] / [QuantityTotal] 

Then in the last step I have to multiply this value by the weights in the other table, and I tryed this:

FinalValue = 
CALCULATE(
SUMX(
    SUMMARIZE(Sales ,Sales[dateSold], weights[Sector]),
    [NPSCalculated] * 
    CALCULATE(
        AVERAGE(weights[weight]))))

But this code cuts out the values of the sector A that exists in the previous months and doesnt exist in the target month. It returns the sum without the part missing.

SaleDate    Sector  bad   good  total   NPS     weight  FinalValue
2021-12     A       2     2     4       0       0,7     0
2021-12     B       1     2     3       0,333   0,3     0,1

I need to get the results of the column FinalValue as above (did it in excel for 2021-12 only), but instead I get the image below, can you guys help me?

[v-chenwuz-msft]

Hi @Renan-rdo ,

You need create a new date table for this visual via Calendar = CALENDAR(MIN('Sales'[dateSold]),MAX('Sales'[dateSold]))

Then add a calculate column named month like the below.

In the table visual, replace [dateSold] with [Month], which will let the table visual get a Cartesian product of [month] and [SECTOR].

And change the parameter in your measure DATESINPERIOD(). Replace the dateSold column with the new date column.

DATESINPERIOD('Sales'[dateSold],MIN('Calendar'[Date]),-3,MONTH))

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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

[Renan-rdo]

Hi @v-chenwuz-msft, thanks for the reply!

Well, creating this table really solved the issue of computing all values, now we are close to what I need, but the final value is still not correct. 

Basically this is the operation:
FinalValue = NPSCalculated * Weight

And so, it should be:
FinalValue = 0,33 * 0,3
FinalValue = 0,1

I think we are closer to the solution, but I still cant find whats wrong with it. Looks like the value is duplicated.

Do you have any idea on how to solve this final step? Again, all the rest looks just perfect, thank you for that!

[ChenwuZhu_Gmail]

Hi @Renan-rdo ,

Maybe you can modify the code like above screenshot shows.

Best Regards

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

[Renan-rdo]

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 · Accepted solution]

Hi @Renan-rdo ,

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.

[Renan-rdo]

@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!