Calculated Column - using it as a slicer for total (aggregated) values

[Anonymous]

Hello  -  The picture below illustrates my issue.  

I have a slicer on my page and my idea is that I want it to filter all orders with a TOTAL value of more than, or less than, $100k.  

Instead, what it is doing is filtering orders that have order lines less than a $100k  -  which is not what I want.   I want the slicer to filter the orders based on the order's total value, not the individual line value.   Any suggestions?

[v-luwang-msft · Accepted solution]

Hi @Anonymous ，

According your provided,I feel you want to create a new column base on table :

Over_Under$10K = 
VAR SUM1 =
    CALCULATE (
        SUM ( 'Table'[Order Value] ),
        FILTER ( 'Table', 'Table'[Order Number] = EARLIER ( 'Table'[Order Number] ) )
    )
RETURN
    IF ( SUM1 < 100000, "LESS $10K", "Over $10K" )

Final output:

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[amitchandak]

@Anonymous , You need to create a measure like example

Measure =
var _sum = calculate(sum('Table'[Orader_value]))
var _1 = Switch (selectedvalues(Slicer[Slicer]) ,
"Less Than 100K", 100000,
"Less Than 200K", 200000,
500000
)
return

Sumx ( filter(values( 'Table'[Order Number]), _sum<_1 ),_sum)

or refer to dynamic segmentation

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k