Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

How to do filter after filtering in the expression?

Hello,

 

I have a matrix with Region, Report as the dimension and the expression is given below;

 
Test Value =

 

IF(

(SELECTEDVALUE(POC[Report]) = "Gross Profit in % of Sales"),

(CALCULATE(SUM(POC[Value ($)]), FILTER(POC, POC[Report] = " Gross Profit"))

/

CALCULATE(SUM(POC[Value ($)]), FILTER(POC, POC[Report] = " Sales"))) ,

Sum(POC[Value ($)])

)

 

Logic: For all the Report Parameters the Values = Sum(Values) except for Gross Profit in % of Sales.

For Gross Profit in % of Sales, the value is Sum(Gross Profit)/Sum(Sales)

 

But the Report Parameter = "Gross Profit in % of Sales" is filtering out from my table as shown below;

 

 

 

 

 

 

 

 

 

What wrong am I doing here? 

 

any help is appreciated

 

thanks

Bhavesh

Need Help
Show and Tell
Tips and Tricks

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous

    As tested with my example data, some parts of the formula are wrong to result as you expected.

     

    SELECTEDVALUE = IF(SELECTEDVALUE(Sheet4[report])="Gross Profit in % of Sales",1,0)
    
Gross Profit = CALCULATE(SUM(Sheet4[value]), FILTER(Sheet4,Sheet4[report]= "Gross Profit"))
    
Sales = CALCULATE(SUM(Sheet4[value]), FILTER(Sheet4,Sheet4[report]= "Sales"))
    
g/s = [Gross Profit]/[Sales]

     

    I can't find Report Parameter = "Gross Profit in % of Sales" in the table.

    I'm confused by the  this sentence:

    But the Report Parameter = "Gross Profit in % of Sales" is filtering out from my table

     

    And what you'd like to see in the chart, could you kindly give me an example of dataset and result visual which you could create  manualy in the excel to give me some refernce?

     

    Best Regards

    Maggie