How to create a measure to filter sales >1000 ?

[Paulyeo11]

Hi All

I have a pivot table , i like to filter those customer buying amount > 1000

https://www.dropbox.com/s/0w75spv54l79ar1/PBT_V01021%20Show%20customer%20buying%20more%20then%201000...

Above is my PBI file

Paul

[AlB · Accepted solution]

@Paulyeo11 

See it all at work in the attached file.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[parry2k]

@Paulyeo11 Thanks! I would avoid using a visual level filter using measures but rather create measures (the way we did in this example) and now you can use this measure in many visuals.

Glad it worked out for you 👍 Cheers!

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

[parry2k]

@Paulyeo11 there are already good solution, here is an optimized DAX

Sales Above Limit Only = 
VAR __limit = 1000 --set a limit
RETURN
SUMX (  
    SUMMARIZE ( --summarize sales by customer
        SALES, 
        SALES[COMPANY], 
        "__Sales", [SALES_] 
    ),  
    IF ( [__Sales] > __limit, [__Sales] ) --return Sales where Sales is greater than limi 
)

And output will be:

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

[parry2k · Accepted solution]

@Paulyeo11 you need to use this measure in your visual instead of filtering on it, see attached.

[Paulyeo11]

Hi Parry

Now i learn one more new approach. This is even better , don't need to add expression at filter. 

Thank you very much. Also your scipt very neat.

Paul Yeo

[Paulyeo11]

Hi Parry

Thank you for sharing the expression.

I try to use your expression on my PBI file below , it return null

https://www.dropbox.com/s/fmvuj62gyfwmfib/How%20to%20create%20a%20measure%20to%20filter%20sales%20gr...

[amitchandak]

@Paulyeo11 , Create a measure

Measure = sumx(FILTER(SUMMARIZE(SALES,SALES[COMPANY], "_1", CALCULATE([SALES_], ALLEXCEPT(SALES,SALES[COMPANY])) ,"_2",[SALES_]),[_1] >1000),[_2])

File attached after signature

like

[AlB · Accepted solution]

@Paulyeo11 

See it all at work in the attached file.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

Hi @Paulyeo11 

You can create a measure to filter the visual

1.  Create this measure:

ShowMeasure =
VAR salesTotal_ =
    CALCULATE ( [SALES_], ALL ( SALES[BRAND] ) )
RETURN
    IF ( salesTotal_ > 1000, 1, 0 )

 2.  Place [ShowMeasure] as a visual filter and choose to show when value is 1

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers