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)     ...
  • 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 )
)
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 )
)
Anonymous's avatar
Anonymous
Not applicable
3 years ago

johnt75  Yayyy!  It worked!!! ðŸ˜€ Thank you so so much!!!! 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi johnt75!

     

    I am looking to add onto this already created dax function you graciously helped me with.  I am looking to add the months as well onto this table, but having it only count for the first order of the date time frame (instead of each month).

     

    This is the formula you helped me with shows (correct result):

     

    But then when I add in months to the table- this is the result I get:

     

    Any ideas on how I could add in a date filter so the formula is only calcuating the first posting of all months in the dataset?

    ex. if customer ordered in August and September, - only count August Order.

     

    Thanks so much,

    Christina