Forum Discussion
Problem with calculation: empty values affect the total result
Dear All,
these two columns have the same calculation base, just in the totals row one shows the Weighted (1st) and the other the Average (2nd), as you can notice, the first one considers the empty value, so the result is not accurate, I need to correct this and show the weighted without considering empty cells or columns with zero values.
Best,
Joaquín
Hello Jkramm1998 ,
You can add logic to your measure to exclude rows where the values are either blank or zero. Here’s a modified DAX formula that should work
SalesTariffWeighted =
VAR NonZeroValues =
FILTER(
'Sales Table',
'Sales Table'[SalesTariff] <> 0 && NOT(ISBLANK('Sales Table'[SalesTariff])))
RETURN
DIVIDE(
SUMX(NonZeroValues, 'Sales Table'[SalesTariff] * 'Sales Table'[Quantity]), -- Weighted sum
SUMX(NonZeroValues, 'Sales Table'[Quantity]) -- Sum of the weights)If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!
Thank You
Dharmendar S
1 Reply
Hello Jkramm1998 ,
You can add logic to your measure to exclude rows where the values are either blank or zero. Here’s a modified DAX formula that should work
SalesTariffWeighted =
VAR NonZeroValues =
FILTER(
'Sales Table',
'Sales Table'[SalesTariff] <> 0 && NOT(ISBLANK('Sales Table'[SalesTariff])))
RETURN
DIVIDE(
SUMX(NonZeroValues, 'Sales Table'[SalesTariff] * 'Sales Table'[Quantity]), -- Weighted sum
SUMX(NonZeroValues, 'Sales Table'[Quantity]) -- Sum of the weights)If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!
Thank You
Dharmendar S