Forum Discussion
DAX : Filter issue
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
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.
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)
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?
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.
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
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
