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

[richchester]

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

[parry2k · Accepted 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.⚡

[richchester]

Thank you parry2k - you are awesome!

Rich

[parry2k]

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

[parry2k]

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

[richchester]

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]

Hi,

Please share some data and show the expected result.

[richchester]

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

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[parry2k]

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

[richchester]

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