Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ryanjparks
Frequent Visitor

DAX Help with Filtered SUM (Slicer)

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!

 

1 ACCEPTED SOLUTION
rautaniket0077
Resolver I
Resolver I

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)

rautaniket0077_0-1679746122337.png

 

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

View solution in original post

5 REPLIES 5
rautaniket0077
Resolver I
Resolver I

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

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
Frequent Visitor

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

rautaniket0077
Resolver I
Resolver I

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)

rautaniket0077_0-1679746122337.png

 

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

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors