Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Summing an IF Statement

Hi Community! 

 

I am looking to create an accurate Sum total column (see highlighted totals below):

 

This Gaps closed measure is: 

 

Gaps Closed = 
IF([Total Qty Baseline]= 0,
1,
0)

 

 

Count of Reorders measure is:

 

Count of Reorders = [New Distriubtion Count AMJ] - [Gaps Closed]

 

I feel like I'm overthinking this, but is there a simple solution to have a Sum total column when using an IF statement?

 

Thank you! 

Christina

 

 

 

 

  • johnt75's avatar
    johnt75
    3 years ago

    The table you use to iterate over in the SUMX needs to match the values you are showing in the visual, so it needs enough columns to uniquely identify each row in the visual. If you have a fact table holding sales, and dimension tables holding other columns you could do something like

    Gaps Closed =
SUMX (
    SUMMARIZE (
        'Sales',
        'Territory'[Territory Code],
        'Customer'[Customer number],
        'Items'[Item number]
    ),
    IF ( [Total Qty Baseline] = 0, 1, 0 )
)

5 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    You need to iterate over the table so the calculation is done row by row, e.g.

    Gaps Closed =
SUMX ( 'Table', IF ( [Total Qty Baseline] = 0, 1, 0 ) )
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi johnt75!

       

      Thanks for the suggestion.  Still not summing, but I'm guessing it's because I'm using a measure as my table reference.

       

      My total baseline measurement is including a date range.  So between these two dates, sum the qty of units sold.

       

      Total Qty Baseline = CALCULATE(SUM('DOCUMENTS'[Quantity]),DATESBETWEEN('DOCUMENTS'[Posting Date], DATE(2022,10,01),DATE(2023,04,07)))+0

       

       

      New Distribution Qty AMJ: 

       

      Total Qty AMJ = CALCULATE(SUM('DOCUMENTS'[Quantity]),DATESBETWEEN('DOCUMENTS'[Posting Date], DATE(2023,04,08),DATE(2023,06,30)))+0

       

       

      If Baseline qty was zero, then calculate sum AMJ qty 

       

      New Distribution Qty AMJ = 
IF([Total Qty Baseline] = 0,
[Total Qty AMJ],
0)

       

       

      Now I'm trying to show - if baseline qty was zero, then show "1" (meaning new distribution).  

       

      Even trying to use the table "Documents" within the sumx formula, would not allow for 1 to show.  Instead it would count the documents.

       

       

      Thanks again,

      Christina

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        3 years ago

        The table you use to iterate over in the SUMX needs to match the values you are showing in the visual, so it needs enough columns to uniquely identify each row in the visual. If you have a fact table holding sales, and dimension tables holding other columns you could do something like

        Gaps Closed =
SUMX (
    SUMMARIZE (
        'Sales',
        'Territory'[Territory Code],
        'Customer'[Customer number],
        'Items'[Item number]
    ),
    IF ( [Total Qty Baseline] = 0, 1, 0 )
)