The Compare Table in Einstein Analytics is a versatile widget which allows you to combine values with mini bars which makes it a powerful and dense visualization The mini bar charts help your audience to easily identify high and low values in second without reading the actual numbers (low cognitive load). If the real values are added in a separate column you give the user the ability to read out the exact numbers without the need of mouseovers or other interactions. This hybrid is best used when you want to lead your audience quickly to an exact number.

If you want to know more about the Compare Table in general: Carl Brundage wrote an amazing article “Compare Table Unleashed” and an Einstein Analytics Template as an unmanaged package. Rikke Hovgaard has some great practical examples on her blog as well. Definitely worth checking out.

Mini Bar in Compare Tables are scaled per column

Just keep this in mind when you work with minibars. If you show different KPI’s this setting is totally fine but if you have the same KPI for e.g. different timeframes like Quarters or Month you would mislead your audience as all column bars are scaled to the max of each individual column.

How to scale minbars in a Compare Table to the table max

If you are working with the same measure in the Compare Table you need to scale each bar based on the max of the Compare Table regardless in which column the max value is. In my use case, I used the Total-Bar which is the sum of A+B+C on the highest level. Currently, there is no parameter in Einstein Analytics (or I haven’t seen it) you could set, to make this automatically happen. I based my use-case on a previous article which you can find here.

In my workaround, I added a new row “viz_total” to my SAQL where I calculate each time the max of the Compare Table as a reference in the Bar-Columns. Later I simply hide the content with conditional formatting. If you choose the colouring accordingly it looks even like this line always should have been there as it creates an optical “end” of the list. The Conditional Formatting allows you now to overwrite the default “yellow/red” colouring.

SAQL example

I based my use case on the previous article about subtotals which you can find here. To be able to add the last row with “viz_total”  I added another calculation “viz_total” where I calculate the table max before the union. For the Conditional Formatting you need another Bin which you can use later in the parameters, I simply choose 3. Don’t forget an additional Sorting field so you can put the sort order of this line later at the very bottom.

q = load "Meetup2";
q_A = filter q by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in ["1 month ago".."current month"];
q_B = filter q by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in ["2 months ago".."1 month ago"];
q_C = filter q by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in ["3 months ago".."2 months ago"];
result = group q_A by ('Account.Industry', 'Account.BillingCountry') full, q_B by ('Account.Industry', 'Account.BillingCountry') full, q_C by ('Account.Industry', 'Account.BillingCountry');
result = foreach result generate coalesce(q_A.'Account.Industry', q_B.'Account.Industry', q_C.'Account.Industry') as 'Account.Industry', coalesce(q_A.'Account.BillingCountry', q_B.'Account.BillingCountry', q_C.'Account.BillingCountry') as 'Account.BillingCountry', sum(q_A.'Amount') as 'A', sum(q_B.'Amount') as 'B', sum(q_C.'Amount') as 'C',0 as 'Ref', "C" as 'SortOrder';
result = order result by ('Account.Industry' asc, 'Account.BillingCountry' asc);

--/////////////Calculate the Subtotals for each Industry/////////
subtotal = group q_A by 'Account.Industry' full, q_B by ('Account.Industry') full, q_C by ('Account.Industry');
subtotal = foreach subtotal generate coalesce(q_A.'Account.Industry', q_B.'Account.Industry', q_C.'Account.Industry') as 'Account.Industry', sum(q_A.'Amount') as 'A', sum(q_B.'Amount') as 'B', sum(q_C.'Amount') as 'C',"Subtotal "+ coalesce(q_A.'Account.Industry', q_B.'Account.Industry', q_C.'Account.Industry') as 'Account.BillingCountry',1 as 'Ref',"B" as 'SortOrderL1';

--/////////////Calculate the Total Sum for each Industry/////////
grandtotal = group q_A by all full, q_B by all full, q_C by all;
grandtotal = foreach grandtotal generate "Total" as 'Account.Industry' ,sum(q_A.'Amount') as 'A', sum(q_B.'Amount') as 'B', sum(q_C.'Amount') as 'C', "A" as 'SortOrderL2', 2 as 'Ref';

--/////////////Calculate the Viz-Total Sum for each Industry for the reference line which will be hidden in the end (blacked out)/////////
viz_total = group q_A by all full, q_B by all full, q_C by all;
viz_total = foreach viz_total generate "viz_total" as 'Account.Industry' ,sum(q_A.'Amount') as 'A', sum(q_B.'Amount') as 'B', sum(q_C.'Amount') as 'C', "Z" as 'SortOrderL3', 3 as 'Ref';


--/////////////Append all three streams/////////
total = union result, subtotal,grandtotal,viz_total;

--/////////////final query to create the list in the correct sort order/////////
total = group total by ('Account.Industry', 'Account.BillingCountry');
total= foreach total generate total.'Account.Industry' as 'Account.Industry', total.'Account.BillingCountry' as 'Account.BillingCountry', sum(total.'A') as 'A', case when sum('Ref') == 3 then (coalesce(sum(total.'A'),0) + coalesce(sum(total.'B'),0)+ coalesce(sum(total.'C'),0) ) else sum(total.'A') end as 'A-Bar', sum(total.'B') as 'B',case when sum('Ref') == 3 then (coalesce(sum(total.'A'),0) + coalesce(sum(total.'B'),0)+ coalesce(sum(total.'C'),0) ) else sum(total.'B') end as 'B-Bar',sum(total.'C') as 'C',case when sum('Ref') == 3 then (coalesce(sum(total.'A'),0) + coalesce(sum(total.'B'),0)+ coalesce(sum(total.'C'),0) ) else sum(total.'C') end as 'C-Bar', coalesce(sum(total.'A'),0) + coalesce(sum(total.'B'),0)+ coalesce(sum(total.'C'),0) as 'Total', coalesce(sum(total.'A'),0) + coalesce(sum(total.'B'),0)+ coalesce(sum(total.'C'),0) as 'Total-Bar',sum(total.'Ref') as 'Ref', last('SortOrderL1') as 'SortOrderL1',last('SortOrderL2') as 'SortOrderL2',last('SortOrderL3') as 'SortOrderL3';
total= order total by ( 'SortOrderL3' desc,'SortOrderL2' asc,'Account.Industry' desc,'SortOrderL1' asc);

Result with automatically scaled bars on the table total

Once your SAQL is ready you hide all columns which you don’t want to make visible and edit the Conditional Formatting. Here you use the same text and background colour for Bin value = 3 so it looks like a “layout” element. Here we go, as result, you get perfectly scaled bars and the user knows instantly whats going on. I just love whats possible with Compare Tables now;)

 

You found a different solution for this? Please let me know