Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Hi,
Is there a way to identify the transactions?
@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.
@Jake_vc Can you post the sample data as text? It's a pain to retype everything to debug it in a PBIX.
@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 - -
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 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])
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |