Filter sales by price under specific amount

[mekaelj]

 Hi,

I have a table where I have all my sales data. I then have another table with price floors for my different products. I want to create measures where I filter out only sales under and over the floor price respectively. How would I go about doing this?

Regards,

[vanessafvg]

@mekaelj are these tables related at all?  

easy to do when you have a relationship, 

over floor price = calculate(sum(saleamount), saleamount> flooramount)

etc

[mekaelj]

Hi,

Thanks for your reply!

They are related. I think what complicates it a little bit is that I calculate the sales amount from two different tables where I have the sales price in one column and then rebates in another. My sales amount calculation looks like this:

Sales in $ = SUM(Sales Table[Gross Sales]) - SUM(Sales Table[Discounts])

The command you gave me doesn't work for measures from what I can tell, is there a way around this?

Regards,

[vanessafvg]

@mekaelj you can always create a calculated column instead of a measure

[mekaelj]

Hi,

Just did! Still can't get it to work though. 

Using the following formula:

Sales under floor = CALCULATE(SUM(Sales[Sales]);Sales[sales]>Floortable[Floor])

I get this error:

The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.

Regards,

[v-chuncz-msft]

@mekaelj,

You may use RELATED Function or LOOKUPVALUE Function to filter data.

Table =
FILTER ( Sales, Sales[Sales] > RELATED ( Floortable[Floor] ) )

Table =
FILTER (
    Sales,
    Sales[Sales]
        > LOOKUPVALUE ( Floortable[Floor], Floortable[Product], Sales[Product] )
)

[mekaelj]

Hi,

I'm not quite sure how to use this. When I try it as a measure I get 

"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." as an error and I don't understand how I'd go about creating a table for it.

Regards,

[v-chuncz-msft]

@mekaelj,

Add a measure as shown below.

Measure =
SUMX (
    FILTER ( Sales, Sales[Sales] > RELATED ( Floortable[Floor] ) ),
    Sales[Sales]
)

[Anonymous]

@mekaelj

Did you solve your issues?

[Anonymous]

Hi @mekaelj

Try this: Sales under floor = CALCULATE(SUM(Sales[Sales]);SUM(Sales[sales])>SUM(Floortable[Floor]))

Let us know, if it works.

Best

Martin

[mekaelj]

Hi,

That yields the following response:

Too many arguments were passed to the SUM function. The maximum argument count for the function is 1.

Regards,

[vanessafvg]

@mekaelj the best idea might be to actually add the related field (floor to the table) and then do the calculation

does that make sense?

so 1. add new calculated column floor to table

then do calculation

probably is that the grain is different so it doesn't know which one to choose, maybe if you played with the max function it might help.  

[mekaelj]

Hi,

I understand what you mean, but I don't know how to actually do it. 

In my sales query I have like 4.000.000 lines of sales. I assume I'd have to add the floor price to each of these sales? How would I go about doing this? I havenät found a way to create a conditional column between querys.

Regards,