SUM(DISTINCT) or other function?

Here's the shared link to the example I have created on my freemium site:

<iframe src="https://embed.domo.com/cards/W7x0g" width="600" height="600" marginheight="0" marginwidth="0" frameborder="0"></iframe>

To replicate this, go to AppStore and find Blank Brick. Then hit get. It will set it up with Example Data. You can set this to your data and then modify the code to use the dataset instead of my coded data.

There will be three areas to paste code: JavaScript, HTML, and CSS. Here's the code to each:

HTML:

<div id="tableContainer">

    <h1>Project Budget Status</h1>

    <table id="budgetTable">

        <thead>

            <tr>

                <th>Project Name</th>

                <th>Planned Cost</th>

                <th>Forecast Cost</th>

                <th>Status</th>

            </tr>

        </thead>

        <tbody></tbody>

    </table>

</div>

CSS:

#tableContainer {

    margin: 20px;

}


table {

    width: 100%;

    border-collapse: collapse;

}


th, td {

    border: 1px solid #ddd;

    padding: 8px;

}


th {

    background-color: #f2f2f2;

    text-align: left;

}


td {

    text-align: center;

}


.status-over {

    color: red;

}


.status-under {

    color: green;

}

And JavaScript:

// Sample data

let data = [

    { "ProjectName": "Project1", "Month": "2021-10-01T00:00:00", "PlannedCost": 10, "ForecastCost": 10 },

    { "ProjectName": "Project1", "Month": "2021-11-01T00:00:00", "PlannedCost": 15, "ForecastCost": 10 },

    { "ProjectName": "Project1", "Month": "2021-12-01T00:00:00", "PlannedCost": 3, "ForecastCost": 20 },

    { "ProjectName": "Project1", "Month": "2022-01-01T00:00:00", "PlannedCost": 6, "ForecastCost": 20 },

    { "ProjectName": "Project1", "Month": "2022-02-01T00:00:00", "PlannedCost": 10, "ForecastCost": 9 },

    { "ProjectName": "Project2", "Month": "2023-01-01T00:00:00", "PlannedCost": 30, "ForecastCost": 44 },

    { "ProjectName": "Project3", "Month": "2024-07-01T00:00:00", "PlannedCost": 5, "ForecastCost": 3 },

    { "ProjectName": "Project3", "Month": "2024-08-01T00:00:00", "PlannedCost": 6, "ForecastCost": 3 }

];


// Aggregate PlannedCost and ForecastCost by ProjectName

let summary = data.reduce((acc, row) => {

    if (!acc[row.ProjectName]) {

        acc[row.ProjectName] = { PlannedCost: 0, ForecastCost: 0 };

    }

    acc[row.ProjectName].PlannedCost += row.PlannedCost;

    acc[row.ProjectName].ForecastCost += row.ForecastCost;

    return acc;

}, {});


let result = Object.keys(summary).map(projectName => {

    let planned = summary[projectName].PlannedCost;

    let forecast = summary[projectName].ForecastCost;

    return {

        ProjectName: projectName,

        PlannedCost: planned,

        ForecastCost: forecast,

        Status: forecast < planned ? "Under Budget" : "Over Budget"

    };

});


// Populate the table

let tbody = document.querySelector('#budgetTable tbody');


result.forEach(project => {

    let row = document.createElement('tr');

    row.innerHTML = `

        <td>${project.ProjectName}</td>

        <td>${project.PlannedCost}</td>

        <td>${project.ForecastCost}</td>

        <td class="${project.Status === 'Under Budget' ? 'status-under' : 'status-over'}">${project.Status}</td>

    `;

    tbody.appendChild(row);

});

The first table doesn't require an ETL, a fixed function or a distinct function. This is the exact kind of data Domo tends to works best with.

This BM should work:

case
when sum(PlannedCost)<sum(ForecastCost) then 'Over'
when sum(PlannedCost)>sum(ForecastCost) then 'Under'
END

However, the second table, where you get counts of over/under is harder. I would probably end up doing it with an ETL. I'm not sure it's possible to get counts across rows using just BMs, but would be curious if someone else could do it. One way to get the counts by budget is with a bar graph. Using the same BM above for budget, and COUNT(DISTINCT ProjectName):

The key is using the projectname as the series, to get it aggregate at the right level. With a lot of projects, I would probably not use data labels and color code based on budget to avoid business on the graph.

If you are using the brick method, you may need to refresh or reset the dataset0. You can also make new columns with a formula tile. Say for example you set the fields for "Field1" and the formula to ''. The output tile will see that new Field1 and leave it blank.

I'm not sure how you are filtering on the extra columns. I assume you are somewhere populating those columns with values you wish to filter on.

Hey, that's terrific. You could also use a filter on that branch to limit what comes through that path.