How can I calculate a percent of total in a Beast Mode?

Reply
Visitor

How can I calculate a percent of total in a Beast Mode?

I'm trying to perform a percent of total calculation in a Beast Mode column - who can help?

 

More details:

In Excel, I would do something like this:  $B2/SUM($B$2:$B$10)

 

Here's the catch - I have certain "groups" in Column A, for example, that need to be filterable, with the percent of total calculation updating with the filter.

 

Domo performs the filtering gracefully, but I do not know how to do the percent total. This Beast Mode attempt seemed obvious:

 

=`unit sales`/sum(`unit sales`)

 

Problem with the above... when we use it, we just get "no data" or whatever Domo says when you've input a technically valid, though ultimately useless, formula.

 

So, in my head I want to use a "SumIf" function from Excel like this:

 

= `unit sales`/sumif(`group name`,"= the filtered group name",`unit sales`)

 

I know I can do this with a pie chart without any Beast Mode schenanigans, but I want the results displayed side by side in a bar chart.

 

Final example: I have 100 retail locations in three states; ME, NH, VT. In January, Coke had 30% of soda sales across all three states, Pepsi had 50%, other brands = 20%. Now, I want to filter by state. Select ME from the Analyzer box, and those % totals should update (ex: in ME, Coke = 40%, Pepsi = 30%, Other = 30%).  

 

Major Brown Belt

Re: How can I calculate a percent of total in a Beast Mode?

is your x-axis going to be dates or a category? in other words are you going to show trend over time or a snapshot as of a certain date? Trend will be more difficult and i think  you'll have to include your Total (your denominator) as a separate column in your dataset. 

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'
Visitor

Re: How can I calculate a percent of total in a Beast Mode?

Thanks for the reply Godzilla - 

The x-axis is going to be categories, not time. No trend is necessary.

Major Brown Belt

Re: How can I calculate a percent of total in a Beast Mode?

Domo has these things called data label macros that might work for you here. Below is the link to their help section for data label macros.

 

http://wiki.domo.com/confluence/pages/viewpage.action?pageId=37454694

 

I'm assuming you're going to use a stacked bar chart to show the product % breakdown. If so, you can use the %_PERCENT_OF_CATEGORY macro which will automatically do this calculation for you. Once you've selectd the chart type and have added the x-axis, y-axis and the Series fields, go to Data Label Settings under chart properties and in the Text box, type in %_PERCENT_OF_CATEGORY in all caps and then tab out of the field and turn on your data labels by setting the Position and Justification fields. If you hover over that Text box, it will show you in a pop up what other macros are available. 

 

This will do the calculation for you and will display it as the label on the chart and if you hover over the chart, it will still show  your 'unit sales' values. 

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'
Welcome to Dojo

Re: How can I calculate a percent of total in a Beast Mode?

Thanks Godzilla! That is a very cool function and will be very useful. But the nature of this problem is more in how to calculate the percentage (as opposed to just displaying it on the data label). As an example - comparing sales mix at 2 companies. The magnitudes of the 2 companies are vastly different, but converting the sales to percentages makes them comparable - if real values are used, Co 1 would be 2 inches tall and Co 2 would be .5 mm for example - hard to compare.

So how does one add a calc to say that 64% of Co 1's coffee sales are from ground coffee, but Co 2 has 79% of its coffee sales coming from ground coffee? Seems like it should just be sales/SUM(sales) or something along those lines - or in other words, what % is this row of the entire column (picturing it in table view, but the calc should be similar for any view or chart).

 

 

 

 

Welcome to Dojo

Re: How can I calculate a percent of total in a Beast Mode?

Update - I noticed today that there is a chart type "Percent of Total" in both the vertical bar and the horizontal bar groups. That does exactly what we are trying to accomplish - EXCEPT - we need it to have 2 series instead of just 1.

 

But again, if there was a way to calc the percent of total in Beast Mode, it would be easy to graph as many series as one desired.

Yellow Belt

Re: How can I calculate a percent of total in a Beast Mode?

We have a similar issue in creating a year over year variance percentage that can be summarized.  Various customers, and again, massive shifts in scale, but it seems a top level summary percentage would be easier to get to than it is.  I'll keep an eye on this thread and see if either of us find a solution.

Yellow Belt

Re: How can I calculate a percent of total in a Beast Mode?

I found a solution that may help.  We had our volumes in 2 columns (it can be done with one as well), but it looks for the Fiscal Year (our LEFT statement) and assigns it the correct summed volume, and performs the calculation for var% with those statements.  It works, and I can split it up as needed as well as include it in the summary number.  I pulled it from the Sample Beast Mode documentation in the Help Center and modified it for our use case, You can find the information here under Variance % ((CY-PY)/PY)

 

Our formula:

 

((SUM( CASE WHEN (LEFT(`Fiscal Year-Month`,4)) = '2016' THEN `CY Claim Volume` ELSE 0 END)) - (SUM ( CASE WHEN (LEFT(`Fiscal Year-Month`,4)) = '2015' THEN `PY Claim Volume`ELSE 0 END))) / (NULLIF(SUM ( CASE WHEN (LEFT(`Fiscal Year-Month`,4)) = '2015' THEN `PY Claim Volume` ELSE 0 END), 0))

Moderator

Re: How can I calculate a percent of total in a Beast Mode?

@adamd, did any of the above replies help answer your question?

Visitor

Re: How can I calculate a percent of total in a Beast Mode?

I'm having the same problem. There does not seem to be a way to go out one filter criteria here, or have Domo "ignore" a category filter on a % of total. 

We'd like to be able to split a % of total into categories without the category filter changing our "denominator". The only way around this at the moment seems to be adding a static column to the dataset that is the denominator we want and creating the % of total Beast Mode using:
[ metric to calculate ] / MIN( [ denominator column ] ). 

Announcements
Looking for the latest Dojo Community solutions? Click on the "NEW SOLUTIONS' widget title or go to https://dojo.domo.com/solutions Click here!