Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to exclude an entry from visualisation table if both fields are showing zeros

  Raw data                     Country Project ID Sub-project ID Chart of accounts Project Manager ID Variable Cost Fixed Cost         Japan 779966 335566 93416 33 ...
Need Help
Tutorial Requests
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    Anonymous 

     

    Thanks for the sample data. It makes things much easier.

    Seeing the dataset, the data contains rows which, when grouped by the IDs, sum out to 0, This means that filter expressions referenced to row value <> 0 wil still return these rows (they have values but their aggregated sum is 0). Here is an example:

     If you want to exclude these rows since they sum to 0, use the following measure instead to filter the visual in the filter pane (set the value to "is greater or equal to 1")

     

    Exclude Sum to 0 rows =
VAR Summ =
    //This creates a virtual table summarizing the measures by the IDs/fields included
    SUMMARIZE (
        Table1,
        Table1[Project ID],
        Table1[Sub-Project ID],
        Table1[Chart of Accounts],
        Table1[Project Manager ID],
        Table1[Line Type],
        "Fixed", [Sum Fixed Costs],
        "Variable", [Sum Variable Cost]
    )
RETURN
    //The measure filters the virtual table to count rows containing "ACTUAL" and with summarized values which are not 0
    COUNTROWS (
        FILTER (
            Summ,
            Table1[Line Type] = "ACTUAL"
                && OR ( [Fixed] <> 0, [Variable] <> 0 )
        )
    )

     

     

    and you will get this result

     

     

PaulDBrown's avatar
PaulDBrown
Community Champion
5 years ago

Anonymous 

 

Here is one way of doing this.

Create a measure to identify each row by a "fullID", filter the rows to only include rows with either values, and then use this measure in the "filters for this visual" in the filter pane (where the measure returns 1)

 

Include Rows = 
VAR FullID = VALUES('DataTable'[Project ID]) & VALUES('DataTable'[Sub-project ID]) & VALUES('DataTable'[Chart of accounts]) & VALUES('DataTable'[Project Manager ID])
RETURN
COUNTROWS(
    CALCULATETABLE(
            ADDCOLUMNS('DataTable', "ID", FullID), 
               FILTER('DataTable', 
                  OR([Fixed Cost]>0,[Variable Cost] >0))))

 

To get this:

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi PaulDBrown I tried your suggested method. It doesn't quite work on my real dataset. I'm not quite sure if it is because my previous dataset example was too simplified. I am attaching the dataset in Excel below. These are the dataset where some lines work and some doesn't based on your suggested method. 

 

P/S I only want to show "ACTUAL" (as per Pivot).

 

Link to Excel

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    5 years ago

    Anonymous 

     

    Thanks for the sample data. It makes things much easier.

    Seeing the dataset, the data contains rows which, when grouped by the IDs, sum out to 0, This means that filter expressions referenced to row value <> 0 wil still return these rows (they have values but their aggregated sum is 0). Here is an example:

     If you want to exclude these rows since they sum to 0, use the following measure instead to filter the visual in the filter pane (set the value to "is greater or equal to 1")

     

    Exclude Sum to 0 rows =
VAR Summ =
    //This creates a virtual table summarizing the measures by the IDs/fields included
    SUMMARIZE (
        Table1,
        Table1[Project ID],
        Table1[Sub-Project ID],
        Table1[Chart of Accounts],
        Table1[Project Manager ID],
        Table1[Line Type],
        "Fixed", [Sum Fixed Costs],
        "Variable", [Sum Variable Cost]
    )
RETURN
    //The measure filters the virtual table to count rows containing "ACTUAL" and with summarized values which are not 0
    COUNTROWS (
        FILTER (
            Summ,
            Table1[Line Type] = "ACTUAL"
                && OR ( [Fixed] <> 0, [Variable] <> 0 )
        )
    )

     

     

    and you will get this result