Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
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
Solved! Go to Solution.
Hi @Anonymous,
You can try to use following measure formula, it will calculate percent of same fruit name based current food group:
Measure = DIVIDE ( CALCULATE ( COUNTA ( Table[Fruit Name] ), ALLSELECTED ( Table ), VALUES ( Table[Food Group] ), VALUES ( Table[Fruit Name] ) ), CALCULATE ( COUNTA ( Table[Fruit Name] ), ALLSELECTED ( Table ), VALUES ( Table[Food Group] ) ), -1 )
Regards,
Xiaoxin Sheng
Hi,
Try this measure
Percentage = 1/CALCULATE(COUNTROWS(Table1),ALL(Table1[Fruit Name]))
Hi @Anonymous,
You can try to use following measure formula, it will calculate percent of same fruit name based current food group:
Measure = DIVIDE ( CALCULATE ( COUNTA ( Table[Fruit Name] ), ALLSELECTED ( Table ), VALUES ( Table[Food Group] ), VALUES ( Table[Fruit Name] ) ), CALCULATE ( COUNTA ( Table[Fruit Name] ), ALLSELECTED ( Table ), VALUES ( Table[Food Group] ) ), -1 )
Regards,
Xiaoxin Sheng
Hi @Anonymous
What is the point of checking if it's an apple? You seem to want the same behaviour in all cases. Maybe I'm missing something.
Try this for your new column:
Percentage = DIVIDE ( 1, CALCULATE ( COUNT ( Table1[Fruit Name] ), ALL ( Table1[Fruit Name] ) ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |