calculate value with condition and group by

[wadda7]

Hi 

I have transaction table with items  

i want to get the count of each itemid if the status = 0 and category = 3 and it should be  group by itemid and locationid in new table

it should be like this 

[wadda7]

and also should be group by maintable[itemid] and maintable[locationid]

[Mikelytics · Accepted solution]

Hi @wadda7,

please find below a possible solution.

old table (t_6):

formula:

t_6_new = GROUPBY(

FILTER(t_6,t_6[status]="0" && t_6[category]="3"),

t_6[ID],t_6[location],

"count",

COUNTX(CURRENTGROUP(),t_6[ID]))

result (t_6_new):

Best regards

Mikelytics

Did I solve your request? Please mark my post as solution

Appreciate your Kudos.

[wadda7]

work fine with me thank you 

[wadda7]

this error comes 

itemid = text

inventdimid (location) = text 

status = int

category = int 

[Mikelytics]

@wadda7 

since your status and category is int you have to make [status] = 0 && [ category] = 3 without using the ""

Did I solve your request? Please mark my post as solution

Appreciate your Kudos.

[Mikelytics]

^Please check if the columns you are use in the filter are formatted as text or number.

if it is number you do [column] = 1

if it is text you do [column] = "1"

Best regards

Mikelytics

Did I solve your request? Please mark my post as solution

Appreciate your Kudos.

[mwegener]

Hi @wadda7 ,

you could do this e.g. with the following measure

count status 0 = COUNTX(maintable, IF(maintable[status] = 0, 1 , BLANK()))

[wadda7]

i want 2 condtions staus = 0 and category = 3 how to add the second condtion 

[mwegener]

Hi @wadda7 

count status 0 = COUNTX(maintable, IF(maintable[status] = 0 && maintable[category] = 3, 1 , BLANK()))