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.
I'm struggling to create a measure in DAX and I wonder if any of you have some ideas. I'm using Direct Query.
I have an Item table and a Customer table joined to a Sales table.
I have a slicer to allow the user to pick 1 or more items
Item table has 2 columns: ItemID and ItemGroup
Customer table has 2 columns: CustomerID and CustomerName
Sales table has 3 columns: ItemID, CustomerID, Sales$
An itemID belongs to one ItemGroup. An ItemGroup can have any number of items.
I'd like to present a table on my report containing CustomerName, Sales$ for the selected ItemId(s), and Sales$ for the ItemGroup(s) to which the selected item(s) belong.
Consider a customer who has bought 6 different items from me, 3 items from ItemGroup1 and 3 others from ItemGroup2.
In my report, I select a single Item from itemGroup1.
My output for that customers should be a column showing the Sales$ for the selected item, and a column showing the total Sales$ for all 3 items that customer has purchased in ItemGroup1.
I'm struggling with that last part. I know what I'd do in SQL - but the equivalent DAX is eluding me.
select sum(Sales$)
from Sales,
inner join Customer on sales.CustomerID = Customer.CustomerID
inner join Item on Sales.ItemID = Item.itemID
where Item.ItemGroup in (select ItemGroup from Item where ItemID in (~items from slicer~))
Does anyone have any ideas?
Thanks so much for your thoughts!
Rich
Solved! Go to Solution.
@richchester see attached file with the solution, just focus on sales and item table, ignore all other tables in the model.
Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@richchester see attached file with the solution, just focus on sales and item table, ignore all other tables in the model.
Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thank you parry2k - you are awesome!
Rich
@richchester question was clear but the example data and the result you gave were wrong and that tripped this off. Go back and read your reply where you provided the sample data and example result, but your most recent reply does make more sense.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@richchester I don't why it is working for me and also the output example you gave is all wrong:
how come if item1 is selected the sales will be $25, it will be $200
and when item 1 and item 4 selected the sales will be $100 there are no sales for item4, assuming item 5 in sales is item 4, how you expected the group sales to be $300
You have to be very clear in your question.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
The requirement is that I allow a report user to select 1 or more items in a slicer.
The output is to show the total $ amount for the selected items, AND the total $ amount for all sales for items in the same group as the selected items.
If a user selectes Item1 in the slicer, then the report should show the $ for any sales for Item1 ($100) plus the total sales for all items in the same group as item1 (in this case that's ITEMGROUP1 which includes item1 item 2 and item3 - $200).
In the example of Item1 and Item4 selected in the slicer, that would show Item1 $100 plus Item4 $0 = $100 for the item total, and also show the total for the groups associated with Item1 and 4... ITEMGROUP1 and ITEMGROUP2.
iTEMGROUP1 includes item1, item2 and item3... so the total for that group is $200
ITEMGROUP2 includes item4 and item5... so for this customer the total for that group is $100
Group total is therefore $300.
Does that clear up the question?
Rich
Hi,
Please share some data and show the expected result.
Good morning Ashish! Here is some data to consider.
Item Table
ITEM1 ITEMGROUP1
ITEM2 ITEMGROUP1
ITEM3 ITEMGROUP1
ITEM4 ITEMGROUP2
ITEM5 ITEMGROUP2
Sales Table
CUSTOMER1 ITEM1 $100
CUSTOMER1 ITEM2 $25
CUSTOMER1 ITEM3 $75
CUSTOMER1 ITEM5 $100
CUSTOMER1 ITEM6 $200
If the slicer is set to ITEM1, the output should be
Customer Item $ Item Group $
CUSTOMER1 $25 $200
If the slicer is set to ITEM1 and ITEM3:
CUSTOMER1 $175 $200
If the slicer is set to ITEM1 and ITEM4:
CUSTOMER1 $100 $300
Thank you for considering my question!
Rich
Hi,
You may download my PBI file from here.
Hope this helps.
@richchester create a measure for the group sales like this if an item is selected it will give the sales of that item's group if not item is selected will give the total sales.
Group Sales =
VAR __selectedGroup = SELECTEDVALUE ( 'Item'[Group] )
RETURN
CALCULATE ( [Total Sales], ALL ( 'Item' ), 'Item'[Group] = __selectedGroup || __selectedGroup == BLANK() )
Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Good morning! Thank you fur the suggestion. Unfortunately, __selectedGroup is always blank in the formula you indicated, regardless of which Item I pick in the Slicer. So I always receive the total $ for all orders in the table for each customer (due to the Or clause in the filter).
I appreciate any further thoughts you may have!
Rich
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 |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |