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

Thanks Amit. I will check further into the Quick Measures to see if those might help. The Running Total function only gives the option for one field for the totalling, but I would need a total of six of the 12 attributes in the table to form up effective subtotals for the records I have. These would include [Cost Item] (i.e. "Server"), [Cost Type] (Server, Disk, Backup), [From Date], [To Date], etc. I haven't yet determined how (or whether) I can insert additional attributes within the generated code to display these Subtotals.