DAX Help with Filtered SUM (Slicer)

[ryanjparks]

Hey everyone, first time poster here. I am new to Power BI and am in need of some help.

It seems that this should be an easy task, but I have been unsuccessful so far and havent been able to find a solution online.

Basically, I am trying to do a "Customers Not Buying" report, where I have a Slicer that allows the viewer to select Item(s) from the database. I have created a summary table that groups by CustomerID and ItemID, with total Sales for each row.

Here's what I want to do: When I select the slicer Item(s), I want a custom measure to calculate a new sum based on the Items I have selected, but still need to show all the CustomerID's that did not purchase that product.

I have disabled the interaction between the ItemID Slicer and the Table (not a Matrix) so that when you select an item, it does not eliminate the CustomerID's that did not purchase the selected Item. But my DAX measure is not correctly adding up the total sales for the selected item. Here's the DAX measure:

SelectedItemTotal = CALCULATE(

    SUM(SalesbyItem[SalesbyItem]),

    ALLSELECTED(SalesbyItem[ItemID])
    )

I've tried any number of different filters for this measure, and none seem to do what I'm looking to do.

How do I sum the total Sales for the selected ItemID without filtering out the Customers that didnt purchase that ItemID?

This is how I would like the result to look:

CustomerID     Total Sales     Selected Sales

ABC123           $3500            $125

DEF456           $10,000          $0

GHI789           $123,000        $4,500

Thanks for any help!

[rautaniket0077]

please give me the measures which you have created i.e. Total Sales and selected sales.

[ryanjparks]

Turns out I was able to get this to work on Friday. With your help, of course 😉

Basically, I disconnected the relationship between the Item table and the Sales table. I then changed the sum formula to add the individual sale price instead of the Total Sales measure, which fixed the grand total being listed for each of the grouped rows.

Also, the multiple selection works as intended on the slicer, so all is well! Thank you so much for your help!

[ryanjparks]

Any other thoughts from the community? I still haven't been able to solve this. Thanks.

[rautaniket0077 · Accepted solution]

Relate your Data with mine.

Use these two below measures
1) 

Total Sales  =

CALCULATE(

    SUM(Orders[Sales]),

    ALLEXCEPT(Orders,Orders[Customer Name])

)

2) 

Selected Sales =

Var a = SELECTEDVALUE(People[Region],0)

Var b =

CALCULATE(

    [Total Sales],

    FILTER(People,

    People[Region] = a

    )

)

return b+0

3) I have used customer name in the table, showing total sales and selected sales based on the value selected in slicer(in slicer i have put region from people table)

please accept my answer as solution if it solves your issue.

[ryanjparks]

Thanks for the response! So here is what is currently happening after making the suggested changes:

Added Customer to the table: Check

Added Total Sales to the table: Check, but displays the total sales for the entire database, not per customer

Added Selected Sales to the table: Check, but it also displays total sales in the database for the selected Item, not per customer.

So it looks like this:

ItemID: SKU123 (Selected)

CustomerID         Total Sales     Selected Sales

      ABC123       $51,868,650            $3,497.04

      DEF456        $51,868,650            $3,497.04

      GHI789        $51,868,650            $3,497.04

I'm thinking it's a problem with the filter on Total Sales, and it seems it should work as you stated...but I am not sure why.

I should also mention that in your example, you have two separate tables listed, while I have a single table. I attempted to duplicate the way you have it (with two tables), but both measures resulted in the same amount ($51,868,650.41) and changing the selected ItemID did not change the result.

One last caveat: It would be very helpful if I can get this to work with multiple selection in the slicer, but it seems to go to $0 when I selected more than one item. This is a secondary issue though, and not necessary.

Thanks for your help!