Measure or column for identifying "free" and "paid"

[Jake_vc]

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.

[amitchandak]

@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")

[wdx223_Daniel]

@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")

[Jake_vc]

This actually worked; although I'm worried whether processing and file size would be an issue once I add further data.

[Fowmy]

@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 🙂

YouTube  LinkedIn