cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
richchester
Regular Visitor

DAX Question - calculate measure based on characteristics of items selected in a slicer

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

1 ACCEPTED SOLUTION
parry2k
Super User III
Super User III

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






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.





View solution in original post

10 REPLIES 10
parry2k
Super User III
Super User III

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






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.





View solution in original post

Thank you parry2k - you are awesome!

 

Rich

parry2k
Super User III
Super User III

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






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.





parry2k
Super User III
Super User III

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






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

 

 

 

Ashish_Mathur
Super User III
Super User III

Hi,

Please share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
parry2k
Super User III
Super User III

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

 

 






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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors