Need help in adding a calculated column

[Anonymous]

Hi

I want to show the count of Exotic and Non Exotic Fruits and Vegetables. While I got the solution to count Exotic, I need help in counting the non exotic ones. I have an added table and there is just one table with name My_Farm_Data in which there are below mentioned columns Type and Sub Category. I want to add the column “Non Exotic” in which it should return the count of “Fruit” and “Vegetable” which are NOT “Exotic”

Type	Sub Category	Non Exotic
Fruit	Exotic	1
Fruit	Non Exotic
Vegetable	Exotic	1
Fruit	Exotic	1
Vegetable	Non Exotic
Dry Fruits	Imported
Spices
Spices
Spices
Dry Fruits	Imported
Fruit	Non Exotic
Vegetable	Non Exotic
Fruit	Exotic	1
Fruit	Imported

[amitchandak]

@Anonymous , with data not able separate input and out put

Only as column

Non Exotic = if([Type] in {"Fruit","Vegetable"} && [Sub Category] <> "Exotic",1,0)

Can work as column or measure . For column will give total in each row

countx(filter(Table,Table[Type] in {"Fruit","Vegetable"} && Table[Sub Category] <> "Exotic"),Table[Type])

[harshnathani · Accepted solution]

Hi @Anonymous ,

Non Exotic Col = if('Table'[Type] in {"Fruit","Vegetable"} && 'Table'[Sub Category] <> "Exotic",1,0)

NonEXoctic COunt = 
VAR Fruits =
    COUNTX (
        FILTER (
            'Table',
            'Table'[Type]
                IN {
                "Fruit"
            }
                && 'Table'[Sub Category] <> "Exotic"
        ),
        'Table'[Type]
    )
VAR veg =
    COUNTX (
        FILTER (
            'Table',
            'Table'[Type]
                IN {
                "Vegetable"
            }
                && 'Table'[Sub Category] <> "Exotic"
        ),
        'Table'[Type]
    )
RETURN
    SWITCH (
        TRUE (),
        'Table'[Type] = "Fruit" && 'Table'[Sub Category] = "Non Exotic", Fruits,
        'Table'[Type] = "Vegetable" && 'Table'[Sub Category] = "Non Exotic", veg,
        BLANK ()
    )

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[Anonymous]

Thanks. Your solution - "Non Exotic Col" is what am looking for. so this is the approach am following - Am going to Transform > Add Column > Add Custom Column. For some reason am unable to choose the table name in the formula you have shown below 

[harshnathani]

Hi @Anonymous ,

Do not do this in Power Query.

Do this in Power BI Desktop.

Regards,

Harsh Nathani

[Anonymous]

Wow! that was easy, thanks! So what is the difference in adding a custom column and having this approach? when i change my data tomorrow will this still apply?

[harshnathani]

Hi @Anonymous ,

Yes, it will still apply.

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[Anonymous]

Awesome! Thanks so much.

[az38]

Hi @Anonymous 

try

Measure = 
CALCULATE(COUNTROWS('My_Farm_Data'),
'My_Farm_Data'[Type] IN {"Fruit", "Vegetable"}, 'My_Farm_Data'[Sub Category] <> "Exotic"
)