Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

SumX is not working in as expected for total

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%

 

Rakeshss_7-1716822566161.png

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

Rakeshss_6-1716822464095.png

 

 

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%

Rakeshss_8-1716822743807.png

 

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%

Rakeshss_10-1716825677095.png

 

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 !
 

 

 

 

 

 

1 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.

vzhouwenmsft_0-1716884680676.png

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)

 

View solution in original post

3 REPLIES 3
v-zhouwen-msft
Community Support
Community Support

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'.

vzhouwenmsft_2-1716865591830.png

 

vzhouwenmsft_1-1716865525490.png

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

 

 

 

 

 

Anonymous
Not applicable

@v-zhouwen-msft  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 !
 

Hi @Anonymous ,

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

vzhouwenmsft_0-1716884680676.png

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)

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.