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

Reply
Major Blue Belt

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

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!


Accepted Solutions
Major Brown Belt

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

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
Domo Arigato!

**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'

All Replies
Major Brown Belt

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

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
Domo Arigato!

**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'
Major Blue Belt

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

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?

 

Major Brown Belt

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

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.

Domo Arigato!

**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'
Major Red Belt

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

@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)

Jacob Folsom
**Say “Thanks” by clicking the “heart” in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
White Belt

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

Awesome! This works! Smiley Happy Thank you!

Announcements
We want your feedback to improve the Dojo Community! If you are coming to the Dojo Award Ceremony next week Click here to learn more!