Forum Discussion

itsmeanuj's avatar
itsmeanuj
Icon for Helper IV rankHelper IV
3 years ago

Exclude month from Total when sum is 0

Hi,   I have a DAX formula below where I want to include a condition that if [Sum_of_Actuals] for any month is 0, then exclude that month from the total calculation.   FY_IP_DIff = VAR __table...
Need Help
Show and Tell
Tips and Tricks
AmiraBedh's avatar
AmiraBedh
Icon for Super User rankSuper User
3 years ago

Try the following : 

FY_IP_DIff =

VAR __table =

CALCULATETABLE(

SUMMARIZE('2023 Data', [Month], "__value", ABS([Sum_of_Actuals] - [Sum_of_IP])),

FILTER(ALL('2023 Data'[Month]), [Sum_of_Actuals] <> 0) )

RETURN IF( HASONEVALUE('2023 Data'[Month]),

ABS([Sum_of_Actuals] - [Sum_of_IP]),

SUMX(__table, [__value]) )

 

the CALCULATETABLE function is used to calculate the summarized table, but a filter is added to exclude any rows where the Sum_of_Actuals is 0. The FILTER function is used to remove rows where Sum_of_Actuals is 0.