Forum Discussion

Wlloyd's avatar
Wlloyd
Regular Visitor
4 years ago
Solved

Filtering based on Sum of Values

I have a table of approximately 12 columns, one of which is a Percentage attribute. I would like to filter the (visual) table so that records that have six of the values in common that are do not su...
Need Help
  • Wlloyd's avatar
    Wlloyd
    4 years ago

    As an additional, alternate attempt, I modified a code example I found in a video and it seems to work in combination with filtering either the visual or the page.

     

    I created a new column with the following formula:

    RollingCostPctTotal =
    VAR CurrentFROM_FY_MONTH = ACR_COST_BREAKDOWN[FROM_FY_MONTH]
    VAR CurrentTO_FY_MONTH = ACR_COST_BREAKDOWN[TO_FY_MONTH]
    VAR CurrentCOST_ITEM = ACR_COST_BREAKDOWN[COST_ITEM]
    VAR CurrentCOST_TYPE = ACR_COST_BREAKDOWN[COST_TYPE]
    VAR CurrentEXPENSE_CLIENT = ACR_COST_BREAKDOWN[EXPENSE_CLIENT]
    VAR CurrentEXPENSE_RESPONSIBILITY = ACR_COST_BREAKDOWN[EXPENSE_RESPONSIBILITY]
    VAR FilteredTable = FILTER(ACR_COST_BREAKDOWN,
    ACR_COST_BREAKDOWN[FROM_FY_MONTH]=CurrentFROM_FY_MONTH
    && ACR_COST_BREAKDOWN[TO_FY_MONTH] = CurrentTO_FY_MONTH
    && ACR_COST_BREAKDOWN[COST_ITEM] = CurrentCOST_ITEM
    && ACR_COST_BREAKDOWN[COST_TYPE] = CurrentCOST_TYPE
    && ACR_COST_BREAKDOWN[EXPENSE_CLIENT] = CurrentEXPENSE_CLIENT
    && ACR_COST_BREAKDOWN[EXPENSE_RESPONSIBILITY] = CurrentEXPENSE_RESPONSIBILITY )
    RETURN
    Calculate(SUM(ACR_COST_BREAKDOWN[COST_PCT]),FilteredTable)
     
    Applying a filter of <>100 suppresses the rows that are deemed to be correct.
     
    This implementation does not require a table to be replicated, and therefore reduces the time needed to refresh the view of the source tables.
Wlloyd's avatar
Wlloyd
Regular Visitor
4 years ago

As an alternate attempt, I created a Duplicate of the table through the Transform, Grouped the resulting table based on the six attributes and summing the percentage attribute, then filtered the rows of that table based on them <> 100%.

 

I then merged the duplicated table back into the first, matching on the six attributes, with the newly created summarized Percent attribute appended as a new column.

 

I was a bit concerned that this technque would a) lead nowhere, or b) mangle my original table, but it seems to have worked out alright.