Filtering assistance

[Anonymous]

All,

I am sure there is an easy solution to this problem but I cannot find it! Anyways here goes:

We have a database that contains transaction lines, for simplicity we have 3 columns, sub name, group name, and sale value. We want to be able to create a visual on a page that shows all the group names with total sales divided by sub name. We can do this but we would like to filter that so we see only totals above 1000000. However when we filter by sales value we only get results back where the line is above 1000000 not the overall total for that name.

We are hoping to just use DAX or equivalent rather than create a new table,

As I said this surely must be easy but I cannot seem to get it to work.

Thanks

Matt

[Sean · Accepted solution]

@Anonymous Create a Group Sales Measure - which calculates the Total at the Group Level!

Assuming that is the Total level you want to filter You can of course change this to Sub Name

Group Sales Measure =
CALCULATE (
    SUM ( 'Table'[Sale Value] ),
    ALLEXCEPT ( 'Table', 'Table'[Group Name] )
)

Drag this Measure to the Visual level filters and select Show items when the value is: greater than or equal to type 100000

And here's the result:

[kcantor]

Create a calculation for the visual.

'Type A transactions' = CALCULATE([Total Sales], [Line Amount]>1000000) Or something similar should work.

this shows using the filter within the calculation. Other lines will be blank. You will need lookup tables for the sub name, group name, and date so that you can populate the axis appropriately.