Forum Discussion

jimrosser's avatar
jimrosser
Helper III
2 years ago
Solved

Measrue with If statement based on Count

I've got a table that I only want the amount not sum of total for qty on hand for day 7 of the week.  The measure below calculates correctly unless there are multiple entries for the same day of that...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi jimrosser 

    Please try the following Dax:

    RINVT Running Total in DAX = 
VAR _Total = CALCULATE(SUM(store_invt[ty_on_hand_qty]),
    FILTER(
        ALL(calendar_dim),
        'calendar_dim'[wk_day_nbr] = 7),
        VALUES ( 'calendar_dim'[wk_nbr] )
)
RETURN
IF(store_invt[COUNT]=1,_Total,_Total/store_invt[COUNT])

     

    If the formula doesn't solve your problem, please provide sample data that fully covers your issue(pbix file or table) and the expected outcome based on the sample data you provided.

     

     

     

     

     

     

     

     

    Best Regards,

    Jayleny

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • jimrosser's avatar
    jimrosser
    Helper III
    2 years ago

    Rita - here's the table that I'm using.  Week 28 should be 111 not 222.  If the count in the last column is greater than 1 I'd like to divide in the measure by that number.

    item_nbritem_namestore_nbrcal_yr_nmwk_nbrRunning Total in DAXRINVT Running Total in DAXCOUNT
    123456BASIC ITEM1Year 202421 01
    123456BASIC ITEM1Year 202422 01
    123456BASIC ITEM1Year 202423 01
    123456BASIC ITEM1Year 202424 2001
    123456BASIC ITEM1Year 202425201801
    123456BASIC ITEM1Year 202426241561
    123456BASIC ITEM1Year 202427301261
    123456BASIC ITEM1Year 202428152222