Forum Discussion

YannDV's avatar
YannDV
Frequent Visitor
4 years ago
Solved

Calculate function filter doesnt seem to work

Hi everyone, 

 

I have a little filter problem. I think i dont really understand how the calculate function works. 

 
The objectif is to change the selected value of the sales in a slicer, to see how it impacts the net sales for example, but it hasnt anything to do with our problem, just to let you have the context. 
My problem is that I want to compare my sales and simulated sales, but my simulated sales formula doesnt work as I want. 
The sales is function is simply the sum of (Final Product Master Data'[€]) (the columns with my sales)
and the simulated sales is a measure : 
Simulated Sales = CALCULATE(SUM('Final Product Master Data'[€])*'Parameter Simulation Sales'[Parameter Simulation Sales Value],ALL('Final Product Master Data'[Article]))

 

The filter on the top left is used to filter the article (4015001050 for example) or the mold, which has been used to create the article. 

When I select an entire Mold, or a mold with only article, I have the correct number (450,54k in our example) as the simulation paramter is 1, and therefore sales = simulated sales. 

 

The problem is that when I select a mold with more than one article, the simulated sales measure still measure for the entire mold and not only the article selected. I thought it would work has I used the articles as filter in my calculate function. I guess I'm misunderstand something. 

Here is an example of the problem :

So as you can see, simulated sales stays 450k while it should be 1,49k.

 

Does anyone has a solution ? 


Thanks a lot, 

 

Yannick 

 

 

Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    Hi YannDV ,

     

    Your problem is this part of the Simulated Sales measure:

    ALL('Final Product Master Data'[Article])

     

    This is telling DAX to remove all filters on the data when calculating, including the filters applied by your slicers.

    Try this instead:

    Simulated Sales = [Sales] * 'Parameter Simulation Sales'[Parameter Simulation Sales Value]

     

    Pete

2 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    Hi YannDV ,

     

    Your problem is this part of the Simulated Sales measure:

    ALL('Final Product Master Data'[Article])

     

    This is telling DAX to remove all filters on the data when calculating, including the filters applied by your slicers.

    Try this instead:

    Simulated Sales = [Sales] * 'Parameter Simulation Sales'[Parameter Simulation Sales Value]

     

    Pete