DAX : Filter issue

[readyOH]

Hi everyone,

I want to filter a field. For example, Total sales for every month except June

What I did :

CALCULATE(SUM('Table1'[Sales]);FILTER(ALL('Table1'[Month]);'Table1'[Month]<>"June"))

Great.... but it's a cumulative function.

How can I sum sales with filter without using the panel

thanks

[Greg_Deckler]

Can you clarify what you are trying to do? You say you want "Total sales" but you say you don't want it to be cumulative??? I can't make heads or tails about what you want.

[readyOH]

Sorry smoupre

The criteria Month is a bad example. Let say : RevenueType  : Value : Product, services, others

So I can have the total sales by RevenueType product, services, others for each month of the year.

I want the sales for the value "products" and "services"

If i create a mesure SalesExceptOthers : CALCULATE(SUM('Table1'[Sales]);FILTER(ALL('Table1'[RevenueType]);'Table1'[RevenueType]<>"others"))

and then if i use this measure SalesExceptOthers in order to make a chart ( Y : SalesExceptOthers and X : month)

I will have a cumulative value each month. So February = Sales of January + Sales of February. March = Sales of February

What iwant is to have the total sales for each month, filter them with a criteria (for example RevenueType) without using the panel (see image above)

[Greg_Deckler]

So, what it sounds like you want is your Measure coupled with a Matrix visualization. So, select your Product, Month and Measure and then select Matrix. Put your Product and Month in Rows and your Measure in Values. This should give you the effect that you are looking for if I understand what you are looking for.

[readyOH]

thank you

off course it's possible to use a matrix, like we do in Excel.

But what I want is create a measure TotalSalesExceptOthers, and this measure will be used in differents visualisations, such as

basic-area-chart https://support.powerbi.com/knowledgebase/articles/653149-tutorial-basic-area-chart

and for creating other measure. for example the rate : SalesExceptOthers/TotalSales etc

It is a basic request : i guess everybody wants to have a filtered measure (in order to exclude data). But CALCULATE(SUM('Table1'[Sales]);FILTER(ALL('Table1'[RevenueType]);'Table1'[RevenueType]<>"others"))  is a cumulative formula

[konstantinos]

Depends how you use the filters in order to be a cumulative formula.Try not to use ALL inside FILTER..

[readyOH]

yes thanks @RJ and @konstantinos

Without using FILTER, it is not cumulative

so my formula becomes

CALCULATE(SUM('Table1'[Sales]);'Table1'[RevenueType]<>"others"))

[RJ · Accepted solution]

Do you need to use FILTER ?

This works for me. And it ignore filters when made

Sales14 = CALCULATE(sum (SalesDetail[Sales Value £]), CalendarInv[Year] = 2014)

whereas using if doesn't. And filter doens't as well

http://community.powerbi.com/t5/Desktop/Measure-to-ignore-one-filter-ie-Year-2014-when-filter-for-ye...

[ALeef]

Are you talking about using a slicer?  So you would have your fields you want to slice by ("products" and "Services" ), and then your chart/graph/whatever shows Sales by month?