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 ...
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!

     

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 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous ,

       

      Do not do this in Power Query.

       

      Do this in Power BI Desktop.

       

       

       

      Regards,

      Harsh Nathani

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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?