Column not Totaling

[Anonymous]

Hi there,

New to powerbi. I would appreciate your help on why my measure is not summing up correctly.

I calculated a discount allowance based on future sales in table and the discount percentages per customer in a different table:

DC Allowance (CP) =

Var DC = divide(sum(Terms[DC Allowances]),COUNT(Terms[DC Allowances]))

Var Results =

IF(SELECTEDVALUE( SKU[BW] )= "000000000004179200", 0,

DC * [ISV (CP)])

Return

Results

When reviewing each customer individualy my discounts calculate correctly. However when I select all the total does it's own thing.

I've attempted sumx & summaraize functions with no luck. the table below illustrates the issue. my total for Jan for all customers should be 17 972 999.86 with an average DC % of 4.2%

[v-henryk-mstf · Accepted solution]

Hi @Anonymous ,

It looks like the problem with your calculated measure is that it is using the SELECTEDVALUE function to determine the value of the BW column. This function will return the selected value of the BW column for the current filter context, which means that the measure will only be calculated for the selected value of BW and will not be aggregated across all values.

To fix this problem, you can modify your calculated measure to use the SUMX function instead of the SELECTEDVALUE function. The SUMX function allows you to iterate over a table and perform calculations on each row, and then sum the results.

Here's an example of how you can modify your calculated measure to use the SUMX function:

DC Allowance (CP) =
SUMX ( SKU, IF ( SKU[BW] = "000000000004179200", 0, DC * [ISV (CP)] ) )

This measure will iterate over the SKU table and calculate the discount allowance for each row using the IF function. If the BW column is equal to "000000000004179200", the value will be set to 0. Otherwise, the value will be calculated using the DC and ISV (CP) columns. The results will then be summed using the SUMX function.

If the problem is still not resolved, please provide detailed error information and test data. Looking forward to your reply.

Best Regards,
Henry

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