SumX is not working in as expected for total

[Anonymous]

Hello all,

Thanks in advance !

Sorry if my title is not correct on the issue.

Below are my visuals, I am using saeme measure in both the visuals, my out is giving different values in 2 different output out of which 1 is correct.

Case 1 (Correct Output) - I have a slicer selected for respective week selected 5/18/2024 (2024, 20) and I am filtering it at visual level Where CTSP% is "Greater than or equal to 0" and my output for TELFORD = 96.56% and total = 97.48%

After Applying Visual level Filter. my out puts are matching TELFORD = 96.56% and total = 97.48%

Case 2 (Correct Output with NaN) - Here which my output is showing last 12 weeks data from the current week. No slicers are applied here and I am not filtering anything at visual level too and my out is correct. for week 2024, 20,  TELFORD = 96.56% and total = 97.48%

Case 2 (Wrong Out put without NaN for total) - Here is my requirment as few ID's comes with NaN for last 12 weeks but it still shows up in the visual as show above but when I try to get rid of the same using visual level filter as  I did in case 1 Where CTSP% is "Greater than or equal to 0", my outputs are  for week 2024,20 TELFORD = 96.56% and total = 96.22%

Here are my measures using to calculate HW.

Monday_Snapshot_Total =

var selectedMondayDate = MIN(KH_Calendar[Date])+1

VAR WeekDates   = DATEADD(KH_Calendar[Date],MAX(GDM_ProductLocation[Fixed Horizon (Weeks)])*7,DAY)

VAR total = CALCULATE(SUM(EU_OMP_PROD_LOC_PLANVIEW_DAILY_FACT[QUANTITY]),

WeekDates,

EU_OMP_PROD_LOC_PLANVIEW_DAILY_FACT[SNAPSHOT_DATE].[Date] = selectedMondayDate)

RETURN total+0

==============================

Monday_Snapshot_Total_SumX =

SUMX(GDM_ProductLocation,[Monday_Snapshot_Total])

===============================

Saturday_Snapshot_Total =

var selectedMondayDate = MIN(KH_Calendar[Date])+6

VAR WeekDates   = DATEADD(KH_Calendar[Date],MAX(GDM_ProductLocation[Fixed Horizon (Weeks)])*7,DAY)

VAR total = CALCULATE(SUM(EU_OMP_PROD_LOC_PLANVIEW_DAILY_FACT[QUANTITY]),

WeekDates,

EU_OMP_PROD_LOC_PLANVIEW_DAILY_FACT[SNAPSHOT_DATE].[Date] = selectedMondayDate)

RETURN total+0

============================

Saturday_Snapshot_Total_SumX =

SUMX(GDM_ProductLocation,[Saturday_Snapshot_Total])

============================

HW  =

VAR AbsDiff = ABS([Monday_Snapshot_Total_SumX]-[Saturday_Snapshot_Total_SumX])

VAR Max_Denominator = MAX([Monday_Snapshot_Total_SumX],[Saturday_Snapshot_Total_SumX])

 RETURN

1-(AbsDiff/Max_Denominator)

=========================

Hope I a m giving the required information to get this issue resolved.

Thansk in advance !

[Anonymous]

Hi @Anonymous ,

Regarding your question, 'Total' is calculated by excluding the filter criteria of the visualization object. However, if you set the filter conditions in the filter window, it will affect the result of 'Total'. Because this is filtering the source data.

You can take the following approaches:

1.Try adding the filter condition 'CTSP% isGreater than or equal to 0' to the measure.(Instead of setting this filter in the filter window)

2.If the results of the line 'TELFORD - FG2' are all NaN, use Blank() instead of the result and Power BI will automatically omit the line.

Suppose I want to exclude the line '5'.

If there are still issues, can you share the .pbix file without sensitive data?

[Anonymous]

@Anonymous  thanks for your response,

Can you please help me how to add filter condition to Measure. Here is my CTSP measure

HW CTSP  =

VAR AbsDiff = ABS([Monday_Snapshot_Total_SumX]-[Saturday_Snapshot_Total_SumX])

VAR Max_Denominator = MAX([Monday_Snapshot_Total_SumX],[Saturday_Snapshot_Total_SumX])

 RETURN

1-(AbsDiff/Max_Denominator)

Thanks !

[Anonymous · Accepted solution]

Hi @Anonymous ,

Regarding your question, 'NaN' occurs because the numerator and denominator are both 0 and the safe division 'Divide' is not used.

Try modifying your expression

HW CTSP  =
VAR AbsDiff = ABS([Monday_Snapshot_Total_SumX]-[Saturday_Snapshot_Total_SumX])
VAR Max_Denominator = MAX([Monday_Snapshot_Total_SumX],[Saturday_Snapshot_Total_SumX])
VAR _Result = DIVIDE(AbsDiff,Max_Denominator)
 RETURN
IF(ISBLANK(_Result),BLANK(),1 - _Result)