Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Need help in adding a calculated column

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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!

     

8 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi Anonymous 

    try

    Measure = 
CALCULATE(COUNTROWS('My_Farm_Data'),
'My_Farm_Data'[Type] IN {"Fruit", "Vegetable"}, 'My_Farm_Data'[Sub Category] <> "Exotic"
)
  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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])

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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