Beast Mode Help: Summary number to show % of transaction type

swagner

I have a dataset where there is a column name 'Ship Via Group' that contains three distinct values.  One of which, "Counter / WC" I need to show in the summary number for the card - "What percentage of orders are Counter / WC?" 

 

Sample data is attached, note that order number may appear multiple times because orders may have 1 or more items.

 

Here is the beast mode I tried, but am getting no value returned.  I'm a noob... I admit it...

 

(CASE when `Ship Via Group`='%Counter%' then COUNT(DISTINCT `Order Number`) else 0

END) / COUNT(DISTINCT `Order Number`)

 

Any help would be greatly appreciated.  Thank you in advance!

Godiepi

Hi, lets fix your beastmode first to see if that gets you where you want to be. Do this instead: COUNT (DISTINCT CASE WHEN `Ship Via Group` LIKE '%Counter%' THEN `Order Number` ELSE 0 END) / COUNT (DISTINCT `Order Number

Godiepi

Hi, lets fix your beastmode first to see if that gets you where you want to be. Do this instead: COUNT (DISTINCT CASE WHEN `Ship Via Group` LIKE '%Counter%' THEN `Order Number` ELSE 0 END) / COUNT (DISTINCT `Order Number

swagner

I have an issue that I can't seem to remedy... I have a pie chart that is displaying three different order modes.

 

There are a total of 173 unique order numbers:  

• 96 are "Our Truck"
• 58 are "Counter / WC"
• 19 are "UPS / MFT"

I am focused on the "Counter / WC" piece which by dividing the values stated above is 33.5% (58 / 173).

 

• PIE VALUE is Beast Mode:  COUNT(DISTINCT `Order Number`)
• PIE NAME is "Ship Via Group" that contains the text values noted above.

 

Everything checks out in the pie chart (% and value for each item), all is well.

 

The Beast Mode for the summary number (which started this discussion) is overstating the count by 1 which causes the summary number and the chart value to not match.  The summary number is displaying 34.1% (59 / 173).

 

Is this a bug?  I have looked at my data, and don't see any issues - there are 58 orders.

 

Is there a way to subtract 1 in the Beast Mode?

 

Godiepi

Based on the spreadsheet with the data you attached , Can you check the order number 14178051 , it looks like that one is both Counter / WC and Our Truck.

JacobFolsom

@swagner, I recommend testing a slight change to the beastmode to remove the ELSE 0 statement in the numerator because the COUNT DISTINCT will actually count the 0 as a distinct value and thus inflate your top number by 1.

 

COUNT (DISTINCT CASE WHEN `Ship Via Group` LIKE '%Counter%' THEN `Order Number` END) / COUNT (DISTINCT `Order Number)

user05925

Awesome! This works! ? Thank you!