Re: Table Card Total Rows Incorrect

Ahh, I see what is happening here. You have duplicates in your dataset because of the employee id. You won't be able to achieve the result using total row as that is going to either sum or count the fields you have inputted. You will need to create a row in your dataset where you do the aggregation for grand total using a MySQL dataflow or Magic ETL.

You would aggregate it by date and put the "grand total" text under the department field and sum the max values of each department for a specific date, then union it to your final dataset.

Thanks,

Brian

**Please mark "Accept as Solution" if this post solves your problem**Say "Thanks" by clicking the "heart" in the post that helped you.

Re: Table Card Total Rows Incorrect

Try creating new beast modes to calculate staffing level and number of employees

Staffing Level:

sum(staffing_level) / count(distinct ifnull(EmpID,1))

Number of Employees:

count(distinct ifnull(EmpID,0))

Then use these fields in your table instead of the current fields you are using.

You should then still be able to drill down and see EmpID if needed

______________________________________________________________________________________________“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman______________________________________________________________________________________________

Re: Table Card Total Rows Incorrect

Ahh, I see what is happening here. You have duplicates in your dataset because of the employee id. You won't be able to achieve the result using total row as that is going to either sum or count the fields you have inputted. You will need to create a row in your dataset where you do the aggregation for grand total using a MySQL dataflow or Magic ETL.

You would aggregate it by date and put the "grand total" text under the department field and sum the max values of each department for a specific date, then union it to your final dataset.

Thanks,

Brian

**Please mark "Accept as Solution" if this post solves your problem**Say "Thanks" by clicking the "heart" in the post that helped you.

Re: Table Card Total Rows Incorrect

Brian,

I could create and add total rows to the dataflow but then my card has no dynamic capabilities and any card filtering at the department level will not be reflected in the totals.

Originally this card was summarized at the daily level and subtotals/totals worked fine but users are requesting the ability to drill down into the detail of each department and view employees in that total.

Any recommendations how I can summarize the card without losing the ability to drill down to the details?

Re: Table Card Total Rows Incorrect

Try creating new beast modes to calculate staffing level and number of employees

Staffing Level:

sum(staffing_level) / count(distinct ifnull(EmpID,1))

Number of Employees:

count(distinct ifnull(EmpID,0))

Then use these fields in your table instead of the current fields you are using.

You should then still be able to drill down and see EmpID if needed

______________________________________________________________________________________________“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman______________________________________________________________________________________________

Re: Table Card Total Rows Incorrect

Brian,

sum(distinct `staffing_level`) will not work correctly if two departments have the same staffing level. We are attempting to sum all rows and divide it by the number of employees. This works for each row but the grand total row for some reason is not a whole number.

In this example using the Main Bar department the sum(`staffing_level`) = 12 and count(distinct `empID`) = 3 and 12/3=4 which is the number I expect. This does not explain why the total row is incorrect on the table where it should be adding 4+6+2 = 12 but is displaying 4.57??