Sales and quantity after discount

[Jake_vc]

Hi team,

Need your help in figuring out how to do the following on Power BI:

Below are 3 separate transactions from a restaurant sales data.

Legend

Club card = given to customers for availing full discount on product
Emp meal = free meal for employee
"TransType
1 = Main item
3 = Discount
"parentDtlID = based on example, if customer avails club card discount, parentDtllD = dtllD"
"mealEmployeeID = employee gets full discount"
guestCheckID = unique

What we need are 2 separate columns (highlighted in blue) :-

1 = Net sales = 0 if items were on discount; else actual sales
2 = Quantity = same as Qty column taking into effect the discounted items as well

[Jake_vc]

Hi,

Is there a way to identify the transactions?

[Jake_vc]

@Greg_Deckler Amazing!!

The discount part works perfectly.

The Employee discount comes out correctly.

We're still facing issues on the Club card discount part.

Based on the above, dtlID and parentDtlID of "21"(highlighted in yellow) (amount = 2.15) should only be removed.

But with the formula provided, the whole check/transaction becomes "0" instead of 11.95.

[Greg_Deckler]

@Jake_vc Can you post the sample data as text? It's a pain to retype everything to debug it in a PBIX.

[Jake_vc]

@Greg_Deckler Hi

I've attached the sample below in an excel file.

Power BI query sample 

[Jake_vc]

@Greg_Deckler  Sorry about that.

I've removed the extra columns and included the relevant one below.

guestCheckID is a more unique than CheckNo as there were duplicates in the data between stores.

I've made changes in the formula you had given based on that.

store MenuItemName MajorGroup Qty UnitPrice Grosssales DiscountType DiscountAmount NetSales locationID TransType dtlID parentDtlID mealEmployeeID guestCheckID Net sales Quantity
Store3 CST Burger Main 2 2.900 5.800 - 5.800 6271800 1 1 NULL NULL 26244435 5.80 2
Store3 Fresh Fries Sides 1 1.250 1.250 - 1.250 6271800 1 11 NULL NULL 26244435 1.25 1
Store3 Fancy Fries Sides 1 2.000 2.000 - 2.000 6271800 1 12 NULL NULL 26244435 2.00 1
Store3 Cheese Burger Main 1 2.150 2.150 - 2.150 6271800 1 21 NULL NULL 26244435 - 1
Store3 NULL NULL 1 (2.150) (2.150) Club card (2.150) (2.150) 6271800 3 24 21 NULL 26244435 - -
Store3 CST Burger Main 1 2.900 2.900 - 2.900 6271800 1 27 NULL NULL 26244435 2.90 1
Store3 ApplePay NULL 1 11.950 11.950 - 11.950 6271800 2 33 NULL NULL 26244435 - -

[Jake_vc]

I can't seem to post the sample data in a proper format here.

I've only put the relevant columns in order to fit the table.

[Jake_vc]

@Greg_Deckler is SWITCH a possibility?

[Greg_Deckler]

@Jake_vc Maybe:

Net sales column =
  VAR __Check = [CheckNo]
  VAR __Discount = COUNTROWS(FILTER('Table',[CheckNo] = __Check && [DiscountType] <> BLANK())
RETURN
  IF(__Discount > 0,0,[GrossSales])


Quantity column = IF([DiscountType] <> BLANK(), 0,[Qty])