Measrue with If statement based on Count

[jimrosser]

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 week.   is there a way to modify the formula so that it will count the occurences and if more than 1 divide by the count number?  If i replace Sum with Count i can see that in week 28 it returns 2 while all the other weeks are only returning 1.

RINVT Running Total in DAX = CALCULATE(SUM(store_invt[ty_on_hand_qty]),

    FILTER(

        ALL(calendar_dim),

        'calendar_dim'[wk_day_nbr] = 7),

        VALUES ( 'calendar_dim'[wk_nbr] )

)

[Anonymous · Accepted solution]

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.

[Ritaf1983]

Hi @jimrosser 

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

[jimrosser]

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_nbr	item_name	store_nbr	cal_yr_nm	wk_nbr	Running Total in DAX	RINVT Running Total in DAX	COUNT
123456	BASIC ITEM	1	Year 2024	21		0	1
123456	BASIC ITEM	1	Year 2024	22		0	1
123456	BASIC ITEM	1	Year 2024	23		0	1
123456	BASIC ITEM	1	Year 2024	24		200	1
123456	BASIC ITEM	1	Year 2024	25	20	180	1
123456	BASIC ITEM	1	Year 2024	26	24	156	1
123456	BASIC ITEM	1	Year 2024	27	30	126	1
123456	BASIC ITEM	1	Year 2024	28	15	222	2