Forum Discussion

ryanjparks's avatar
ryanjparks
Icon for Helper I rankHelper I
3 years ago
Solved

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 fi...
Need Help
  • rautaniket0077's avatar
    rautaniket0077
    3 years ago

    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.

rautaniket0077's avatar
rautaniket0077
Icon for Resolver I rankResolver I
3 years ago

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's avatar
    ryanjparks
    Icon for Helper I rankHelper I
    3 years ago

    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!