Hi there, I want to create a KPI where either the Total Revenue or the Total Comm Paid or the % Achieved calculation depending on what is selected in the Calculation Filter, is higher or lower than the Average of all the calculations. I have this calculation:
Avg Selected = CALCULATE([Selected Metric],REMOVEFILTERS(‘Date’[Start Month]),REMOVEFILTERS(SalesReps[Name]))
It’s not removing filters. It still maintains row context. Please can someone help me and put me out of my misery.
Hi @SammiP, thanks for providing the pbix file. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.
Thanks
Hi @SammiP
Could you please use the below formula instead, Looks like you are removing filter from the wrong table.
Also note that - You need to remove filters in similar fashion from SalesRep[name] from all masures which are there in your calculation group in order to nullify selection of names from the report .
Total Revenue APP = CALCULATE(
SUM( Commissions_baseTable[Applied Revenue] ),
REMOVEFILTERS( SalesReps[Name] )
)
Welcome to the forum @Dhrubojit_Goswami thank you for that wonderful answer. 
Hello @SammiP, did the response provided above help you solve your query?
If not, how far did you get and what kind of help you need further?
If yes, kindly mark as solution the answer that solved your query.
Hi @Dhrubojit_Goswami, we’ve noticed that no response has been received from you since October 20.
We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Hi @EnterpriseDNA , I think you have tagged the wrong person . .I have am not the requestor of this qn . I helped answering it
Hi there,
It landed up changing the entire calc because I couldn’t get it to work. But I need to remove the filters. for the table I am displaying. I don’t know how.
Hi,
You can remove it by using isinscope function:
Like this ::
if(isinscope(tb[Total applied]),ans,blank())
The above dax will print blank as soon as the scope of that column gone.
Thanks,
Anurag
Hi Anurag, I used the Inscope function for something else. What I really want is for the 32,185 to appear at every row for the sales person. But I don’t know which filters to remove. I have tried.
Revenue for Table =
VAR AllTotalRevenue = CALCULATE([Total Applied Revenue],ALL(Commissions_baseTable))
return
AllTotalRevenue
See this picture:
HI,
Can you share work in progress pbix i think i help you better after looking on your data model.
Thanks,
Anurag
Hi Anurage,
Thanks for trying to help me. Here are the files. I created a % target which removed the filters but somehow when I place the measure in the table (details) it explodes the data. I want to work out the sales person’s target, not per deal but by the entire month.
Commercial_CommissionReport_DW_27_Oct.pbix (2.0 MB)
CommercialChannel_SalesRep.xlsx (253.0 KB)
Consumer Key Account Manager.xlsx (11.1 KB)
Contract_Term.xlsx (10.3 KB)
01_09_2021_VAS.xlsx (4.9 MB)
Hi @SammiP - Bit confused here, there are multiple Target measure in your file. Which measure are you referring to and in which tab you want to use it. Is it detail tab or Overview tab.
Please explain here and also what is the expected result.
Thanks
Ankit J
@SammiP I am sharing my tentative-solution which is based on some assumption of your expectations that somthing is wrong with % of target calculation.
Commercial_CommissionReport_DW_27_Oct.pbix (2.0 MB)
Please see if the attached pbix and udpated measure helps in your requirement.
Sorry, I am not chrisp on your ask .
if it deosn’t meet your need pls let me know what is the -Expected result in some simple table illustration (Just illustration of what is happening and what value you expect may be for a subset of data)
Hello @SammiP, if the inquiry was answered kindly mark the response that you think best helped you. Thank you so much.
Hi Ankit J,
The reason why there were multiple target measures is because I used the IsinScope Function and when I do that, when the measure is not in scope with the Month it calculates the Target as a YTD figure. I had a % of Target Achieved at the top of the report. It means I have to have multiple measures of the same thing. How I landed up using isinscope is that I wanted a Jan - Dec Columns in a Matrix with the YTD to Figure at the end of the matrix. Not in every single month.
Hi @SammiP ! We noticed that your inquiry was left unsolved for quite some time now.
Looks like your inquiry was out of the experts and users’ bounds.
We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.
While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!
