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 sum to 100% are displayed in the resulting table (i.e. summed for the percentage attribute at the level of detail displayed, rather than the value for the individual record in the table).

 

Is there a Column or Measure that I could use to display the summed % value, or would I have to build a new Dimension table that allows for this calculation to be performed, then use Related/RelatedTable to draw this value back into the original Fact table?

 

As a potentially complicating factor, there is no index on the Fact table, and if I were to build a new Dimension table, related records would be based on the values in six attributes.

 

Any helpful thoughts?

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.

4 Replies

  • 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.
  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Wlloyd , Check if the running total from quick measure can help ?

     

    You need measures like

     

    cumm = calculate(COUNT(Sheet1[Index]), filter(allselected(Sheet1),Sheet1[Index] <=max(Sheet1[Index])))
    total = calculate(COUNT(Sheet1[Index]), allselected(Sheet1))

    Cumm % = divide([cumm],[total])

     

     

    or

     

    cumm = calculate(COUNT(Sheet1[Index]), filter(allselected(Sheet1),Sheet1[date] <=max(Sheet1[date])))
    total = calculate(COUNT(Sheet1[Index]), allselected(Sheet1))

    Cumm % = divide([cumm],[total])

     

    using date table

     

    cumm = calculate(COUNT(Sheet1[Index]), filter(allselected(date),Sheet1[date] <=max(date[date])))
    total = calculate(COUNT(Sheet1[Index]), allselected(Sheet1))

    Cumm % = divide([cumm],[total])

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

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