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 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.