Multi filters

BORUCH

Active Member
Joined
Mar 1, 2016
Messages
273
HI
I HAVE THIS FORMULA

Sales Flag:=IF(NOT(ISEMPTY(CALCULATETABLE('sales','sales'[Qty Ord] >0))),"Had Sales","Had No Sales")

Two questions

#1
how can i have this formula also do a filter for [qty ord] is less the ZERO
I want it to tell me only if its less then zero or greater the zero not equal zero

#2
HOW CAN I HAVE THIS AS A SLICER (FILTER)
HAD SALES
HAD NO SALES
 

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,189
Regarding 1, why do you have this formula? What is its purpose? The reason I ask is that Power Pivot is different to Excel, and you don't need to replicate Excel concepts - there can be different/better approaches.
 

BORUCH

Active Member
Joined
Mar 1, 2016
Messages
273
Regarding 1, why do you have this formula? What is its purpose? The reason I ask is that Power Pivot is different to Excel, and you don't need to replicate Excel concepts - there can be different/better approaches.
This formula tells me if an item was sold for that customer, or for that month, or whatever, it looks at my detail invoice history table and tells me if that item is there (IF IT WAS SOLD)
 

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,189
So why do you need it? If you put items in a pivot table for a month, if it sold you will see it, it if didn't sell you won't see it. If you want to see products that didn't sell, you can write a measure for that too.
 

BORUCH

Active Member
Joined
Mar 1, 2016
Messages
273
ok let me explain

I have my products table connected to my invoice history detail table, so I can drag my item # from my products table, and then drag my sales flag to the values section, and I can filter by my customer # in my detail table to see if that customer bought that product this month etc.

If you have a better formula please let me know, this is a very simple formula I just want to add another filter, and be able to filter by “has sales”, and “has no sales”
 

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,189
Haven't confirmed, but I assume your formula is a calc column. The problem is that it is a static result based on the latest data. If you want to see customers that didn't purchase last month, or time series trend you can't do it with this approach?

try this as a measure for products that sell

=calculate(countrows(products),InvoiceHistoryTableName)
and products that don't sell

=calculate(countrows(products),filter(products,[total Sales measure]=0))
 
Last edited:

BORUCH

Active Member
Joined
Mar 1, 2016
Messages
273
Haven't confirmed, but I assume your formula is a calc column. The problem is that it is a static result based on the latest data. If you want to see customers that didn't purchase last month, or time series trend you can't do it with this approach?

try this as a measure for products that sell

=calculate(countrows(products),InvoiceHistoryTableName)
and products that don't sell

=calculate(countrows(products),filter(products,[total Sales measure]=0))
Thanks for your time

I would like to keep my original formula, which works best for me!, just to have the ability to have two filters, and have the ability to have a slicer, with two options, have sales, and have no sales.
this is the easiest way for me, any suggestions maybe a disconnected table is appreciated.

Thanks
 

Forum statistics

Threads
1,085,009
Messages
5,381,186
Members
401,718
Latest member
Ereese20

Some videos you may like

This Week's Hot Topics

Top