Forum Discussion

johnfa's avatar
johnfa
Frequent Visitor
6 years ago
Solved

Measure to sum another measure problem

Hi there I am new to DAX and having problems calculating a measure to sum another measure and was hoping someone could point me in the right direction.  I have simplified the data below to show th...
Need Help
  • YJ's avatar
    YJ
    6 years ago

    Hi ,

    Under Modeling , New Table put this in:

     

     Tb_Products_w_no_Sales = SUMMARIZECOLUMNS(Table1[Date ],

    (Filter (
    SUMMARIZECOLUMNS(Table1[Date ],Table1[Product],"ZERO_S",IF(SUMX(Table1,Table1[Sales])=0,0,1)),[ZERO_S]=0
    )
    ),"Products_w_no_Sales",DISTINCTCOUNT(Table1[Product])
    )
     
    *overall idea, ZERO_S will give 0 for rows of products with no sales, and after do a distinct count on product
    where Table1 is your data.
     
    This should be what you want. i believe 
    02/01/20192
    is not correct, it should be 3. but let me know.
     
    If you need a 0 on the missing dates, link it to a date table or left join it.
     
    Regards
     
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi johnfa ,

     

    We can create a calcualted column and new a measure based on it.

    per product = 
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        'Table',
        'Table'[Date ] = EARLIER ( 'Table'[Date ] )
            && 'Table'[Product] = EARLIER ( 'Table'[Product] )
    )
)

    Measure = CALCULATE(DISTINCTCOUNT('Table'[Product]),FILTER('Table','Table'[per product] = 0))

     

v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi johnfa ,

 

We can create a calcualted column and new a measure based on it.

per product = 
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        'Table',
        'Table'[Date ] = EARLIER ( 'Table'[Date ] )
            && 'Table'[Product] = EARLIER ( 'Table'[Product] )
    )
)

Measure = CALCULATE(DISTINCTCOUNT('Table'[Product]),FILTER('Table','Table'[per product] = 0))