Forum Discussion
Solved
Sum But Exclude Only Some Filter Context
Hello, I'm sure this is a simple question to answer but I've looked everywhere and cannot understand what I'm doing wrong. I work for an RV manufacturer. I have fact data that includes Invoice ...
Hi Rena,
So that would be a solution and is one I hadn't thought of so thank you! I did manage to find a way to get it to work somehow with the following equation:Sum Of Sales Allowance v4 =CALCULATE(SUM(FT_Sales[LINEAMOUNT]),REMOVEFILTERS(FT_Sales),FT_Sales[SALESID] = SELECTEDVALUE(FT_Sales[SALESID]),FT_Sales[ITEMID] = "SA")As far as I can tell, I've removed all the filters from my sales table - I had to do this because I needed to select line number 1 as my header line but couldn't sum information from other lines if this filter was here - then reinstated the filter on sales ID which makes sure I only grab the Sales Allowance record associated with any individual order, before applying a filter for the specific item i was looking for.
I appreciate you taking the time to look at and respond to my problem. I hope this helps anyone else looking to answer this problem.
Cheers,
Nick
Hi NickAdmin2 ,
As checked your screenshot, they are different ITEMID(BC and SA). Hence the table visual display two rows instead of one row.
If you want to display only one row, you can create a column as below with the same value to replace the field [ITEMGROUPID]. Please find the details in the attachment.
Items =
VAR _sales = 'FT_Sales'[SALESID]
VAR _tab =
CALCULATETABLE (
VALUES ( 'FT_Sales'[ITEMID] ),
FILTER ( 'FT_Sales', 'FT_Sales'[SALESID] = _sales )
)
RETURN
CONCATENATEX (
FILTER (
ALLSELECTED ( 'FT_Sales'[SALESID], 'FT_Sales'[ITEMID] ),
'FT_Sales'[SALESID] = _sales
),
'FT_Sales'[ITEMID],
","
)
Best Regards
Hi Rena,
So that would be a solution and is one I hadn't thought of so thank you! I did manage to find a way to get it to work somehow with the following equation:
Sum Of Sales Allowance v4 =
CALCULATE(
SUM(FT_Sales[LINEAMOUNT]),
REMOVEFILTERS(FT_Sales),
FT_Sales[SALESID] = SELECTEDVALUE(FT_Sales[SALESID]),
FT_Sales[ITEMID] = "SA"
)
As far as I can tell, I've removed all the filters from my sales table - I had to do this because I needed to select line number 1 as my header line but couldn't sum information from other lines if this filter was here - then reinstated the filter on sales ID which makes sure I only grab the Sales Allowance record associated with any individual order, before applying a filter for the specific item i was looking for.
I appreciate you taking the time to look at and respond to my problem. I hope this helps anyone else looking to answer this problem.
Cheers,
Nick
I appreciate you taking the time to look at and respond to my problem. I hope this helps anyone else looking to answer this problem.
Cheers,
Nick