Forum Discussion
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 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.
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.
3 Replies
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.
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
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
