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've been using Power BI for the past couple of months and this group has been helpful in finding solutions for my beginner queries.
I was hoping I might be able to find a solution to my query from you guys.
We work in the food and beverage industry and above is from the Oracle data that we get daily.
We have "Main", "Beverages", Toppings", "Dessert" and "Sides" as the groupings.
MenuItemName | MajorGroup | FamilyGroup | RvcName | QtySold | NetSales |
Bacon | Toppings | Cheese Burger | Delivery | 2 | 1.300 |
Bacon | Toppings | Paid Topping | Delivery | 1 | 0.650 |
Balsamic Mustard for San | Toppings | Free Topping | Delivery | 3 | - |
Balsamic Mustard On The | Toppings | On The Side | Delivery | 1 | - |
Our main dashboard page would look at "MajorGroup" and then further analysis would be done on "MenuItemName".
Our issue is that, we need to identify and segregate "Free" and "Paid" from the above data.
I guess having a column in Power query would be the ideal.
Ideally something like this:
MenuItemName | MajorGroup | FamilyGroup | RvcName | QtySold | NetSales | Type |
Bacon | Toppings | Cheese Burger | Delivery | 2 | 1.300 | Paid |
Bacon | Toppings | Paid Topping | Delivery | 1 | 0.650 | Paid |
Balsamic Mustard for San | Toppings | Free Topping | Delivery | 3 | - | Free |
Balsamic Mustard On The | Toppings | On The Side | Delivery | 1 | - | Free |
So, is the above possible through a measure or a column?
Really appreciate your help on this.
@Jake_vc , Try a new column like
new Column =
var _max =sumx(filter(Table, [MenuItemName] =earlier([MenuItemName])), [NetSales]) +0
return
if(_max>0 ,"Paid" , "Free")
@Jake_vc you can add a calculated column in the table
=IF([NetSales]>0,"Paid","Free")
or create a measure and put it in the value area.
=IF(SUM(table[NetSales])>0,"Paid","Free")
This actually worked; although I'm worried whether processing and file size would be an issue once I add further data.
@Jake_vc
Can you explain the logic, how the Type column should be created?
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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 |
---|---|
40 | |
20 | |
19 | |
15 | |
15 |
User | Count |
---|---|
47 | |
28 | |
25 | |
17 | |
17 |