Forum Discussion

NickAdmin2's avatar
NickAdmin2
Frequent Visitor
2 years ago
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 Numbers, Invoice Lines, and Item codes in a sales table. Item code relates to an Item dimension table which inludes an item group. Invoice Line 1 will always be the RV model number, and other lines on the invoice will be various packages, options, allowances, etc.

 

I'm working on a report that will show this line level information at a header level. I want only have one row for each unit, but I'll be able to see the allowances, options, etc. totals at that same level. Below is a screenshot of what I'm trying to accomplish. You'll notice Item Group ID is missing from this visual

 

Here you can see an example of what my data looks like:

 

I've created the following equation which to my knowledge sums the line amount after removing the line number filter from the page and then filtering for the specific sales allowance item ID = "SA": 

 

Sum Of Sales Allowance v3 =
CALCULATE(
    SUM(FT_Sales[LINEAMOUNT]),
    REMOVEFILTERS(FT_Sales[LINENUM]),
    FT_Sales[ITEMID] = "SA"
)
 
The issue arises when I add Item Group ID to my visual. My end user wants to see that item ID '123abc' is part of item group 'BC'. However, when I add item group ID to the visual, I start getting two records instead of one. That Sales Allowance line is no longer with item BC, but at item group S-SA, even though I've filtered the visual for line number = 1 and that S-SA line is on line 6 as you can see above.

 

Any idea what I'm doing wrong here? I'd appreciate any help you all can provide!

 

Need Help
Tips and Tricks
  • NickAdmin2's avatar
    NickAdmin2
    2 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

    • NickAdmin2's avatar
      NickAdmin2
      Frequent Visitor
      2 years ago

      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