How to create a measure that does a conditional, and a groupby and a count of that group by result (DAX/PowerBI)

1
votes

I recently started using PowerBI and learning DAX. I want to convert a conditional pseudocode into DAX, that would result in a column named "Percentage". So my table consist of columns Company (Dole, Dyson, Monsanto, and etc...), Fruit Name (Apples, Bananas, Pears, Tomatos, Cucumbers, and etc.), and Food Group (Vegetable, Meats, and etc.)

Company | Fruit Name | Food Group    
----------------------------------
Dole    | Apple      |  Fruit 
Dyson   | Chicken    |  Meat
Dole    | Pear       | Fruit
Dole    | Tomato     | Vegetable

Here's the pseudocode for the conditional:

if(FruitName = 'Apple')   //for the rows that have 'Apples'
     then( 1/count( Food Group for Company) )   //percentage of apples within 
                             //Fruits distributed by Company: probably a groupby

else (1/count(Food Group for Company) )           //percentage of value within 'Food Group' by Company

The result would output within the measure, is a column for the percentages.

Company      | Fruit Name | Food Group | Percentage
-----------------------------------------------------
Dole         | Apple      | Fruit      |  33.33%
Dyson        | Chicken    | Meat       |  100%
Dole         | Pear       | Fruit      |  33.33%
Dole         | Banana     | Fruit      |  33.33%
Dole         | Tomato     | Vegetable  |  100%

Could you please show me a way to write this conditional in DAX?

Thanks

1
powerbidax

1 Answers

0
votes

I'm not sure I understand why you want a conditional since your then and else are the same.

Anyway, I think you're looking for something like this:

Percentage =
DIVIDE (
    COUNT ( Foods[Fruit] ),
    CALCULATE ( COUNT ( Foods[Fruit] ), ALLEXCEPT ( Foods, Foods[Group] ) )
)

The ALLEXCEPT function removes all of the filter context except for the Group column so you get all the Fruit within that Group.

Result