I have used a table and matrix visual with several columns of data (call these (Supplier, Test case 1, Test case 2 and Test Case 3).
However the totals at the bottom of the table are incorrect due the underlying data i.e.. where I expect to see £10m, the figure generated against Supplier 1 is £100m because it is adding up 10 entries against that supplier - the data being used has been merged from two different data sets with several common columns.
Say there are 20 suppliers and this inflation of the numbers occurs throughout.
So I have used a quick measure for each test case (Test case 1 divide by count of Test case 1, and the same for Test case 2 and test case 3.
I've now used these quick measures and have put these into the table. However the totals at the bottom of the table still adds up all the monetary values and adds up all the counts and takes the total monetary value and divides it by the total count. This is not the total I need.
Is there a way to add a total to each of the columns so that it adds up the values only?
As I am adjusting filters on the visuals, the 20 suppliers will reduce in number, so I can see the table shrink, and I want to be able to see the new totals having made an adjustment to the filters.
Thanks in advance.