how to increase my results of an equation by 1 but only if another value is what I define

[mchilders56]

Here is my formula that I am using to calculate the Qty to order of an item from my database. I want to make it so that if the VI_PS_TKT_HIST_LIN[CAT_COD] (Category code) is equal to a specific value that it adds 1 to the over all number, otherwise it uses this base calculation.  I would have a list of values to reference for the category code as well. For example DOG FOOD, CAT FOOD, etc would be what I want to add more to the order qty

Qty to order 30 = IF( SIGN( ROUNDUP(CALCULATE((45*(SUM(VI_PS_TKT_HIST_LIN[QTY_SOLD])/VALUE([Days Between TKT_HIST])))-SUM(VI_IM_ITEM_WITH_INV[QTY_ON_HND])),0)) <0,0, ROUNDUP(CALCULATE((45*(SUM(VI_PS_TKT_HIST_LIN[QTY_SOLD])/VALUE([Days Between TKT_HIST])))-SUM(VI_IM_ITEM_WITH_INV[QTY_ON_HND])),0))

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[mchilders56]

for my purposes lets use Brand and the category code. so anyhing that has brand ETTA or KOHA I want my column for Qty to order to increase by 1, but for the brand WHIMZEE and WELLNESS I want the original formula to display.

Item Number	Description	Brand	Qty Sold	Qty on Hand	Days on Hand	Qty to order (21 days of supply)	Number of Day to Sell Out
1104802294	NOOTIE PROGILITY CALM BAR	KOHA	175	4	4	17
1543601271	WHIMZ STIX SM	WHIMZEE	129	115	163	0
1543601479	WHIMZ BRUSHZEES XSM	WHIMZEE	105	456	795	0
1543601571	WHIMZ K9 VEGGIE SAUSAGE SMALL	WHIMZEE	79	154	357	0
5008900798	ETTA SAYS BEEF YUMM STICKS	ETTA	69	22	58	0
1543601480	WHIMZ BRUSHZEES SM	WHIMZEE	64	143	409	0
5008900795	ETTA SAYS PORK YUMM STICKS	ETTA	62	19	56	0
5399400105	ETTA SAYS TURKEY YUMM STICKS	ETTA	52	22	77	0
5699500801	ETTA SAYS CHICKEN YUMM STICKS	ETTA	50	17	62	0
5659500565	ETTA K9 PREM BUFFALO CHEW 7in	ETTA	49	10	37	0
7634489998	WELLNESS CAT HEALTHY TKY DUC3z	WELLNESS	49	17	63	0
1543601527	WHIMZ BRUSHZEES MD	WHIMZEE	42	139	606	0
1104802096	KOHA DOG GF TURKEY 13oz	KOHA	35	4	21	1
1543601529	WHIMZ ALLIGATOR SM	WHIMZEE	35	81	424	0
1543601482	WHIMZ BRUSHZEES LG	WHIMZEE	33	6	33	0
7634408863	WELLNESS CAT CHKN&LBSTR 3oz	WELLNESS	31	0	0	4	138

[Tahreem24]

@mchilders56 Use this DAX:

Formula = IF(TableName[Brand] in {"ETTA","KOHA"}, [Order to Qty]+1,[Order to Qty])

[mchilders56]

I get an error for the tableName[Brand] that column cannot be found or used in this expression. I am doing a direct query so the table and column are already defined for me. I think i needs to be a different format but not sure what yet in order to accept that column. I thought maybe value() but that did not work either.

[lbendlin]

 I am doing a direct query

Next time please start with that piece of information. 

Did you switch to mixed mode (with local data model) or does it need to be a measure?

Try this calculated column:

Formula = [Order to Qty] + INT([Brand] in {"ETTA","KOHA"})

[mchilders56]

it's only letting me select things that are measure that I have created. It's not a calculated column that I have made or anything like that. that column in my DB basically has a list of names that I can trying to reference.

[v-linyulu-msft]

Thanks for the reply from lbendlin and Tahreem24,please allow me to provide another insight:

Hi, @mchilders56 

Could you please let us know if lbendlin's response resolved your issue? If it did, kindly accept it as the solution.

1.To facilitate your understanding, I have created the following test data with a storage mode set to DirectQuery:

2.Secondly, I implemented ll's response:

Measure = sumx('Inventory',[QtyToOrder]+INT([Brand] in {"ETTA","KOHA"}))

Of course, you can also try the following measure:

Measure 2 = IF(MAX('Inventory'[Brand]) IN {"ETTA","KOHA"},SUM('Inventory'[QtyToOrder])+1,SUM('Inventory'[QtyToOrder]))

3.Below is the final result:

Best Regards,

Leroy Lu

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

[lbendlin]

measure = sumx(Table,[Order to Qty] + INT([Brand] in {"ETTA","KOHA"}))