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

adamd
adamd Member
in Archive

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%).  

 

Comments

  • Godzilla
    Godzilla Contributor

    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'
  • adamd
    adamd Member

    Thanks for the reply Godzilla - 

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

  • Godzilla
    Godzilla Contributor

    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'
  • eo2112
    eo2112 Member

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

     

     

     

     

  • eo2112
    eo2112 Member

    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.

  • jlafee
    jlafee Member

    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.

  • jlafee
    jlafee Member

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

  • kshah008
    kshah008 Contributor

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

  • seanastrup
    seanastrup Member

    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 ] ). 

  • rado98
    rado98 Contributor

    How abouty something like this (using your example)

     

    sum (Case... 'Coke'.... `unit sales` ) / sum(`unit sales`)

     

    This should allow to filter out States.

    BeastModes cannot use agregate data with non-agreagated data (as far as i understand), which is a royal pita. 

  • [Deleted User]
    [Deleted User] Member

    @ckwright

     

    Any thoughts on this Beast Mode discussion?

    Thanks!

  • swagner
    swagner Contributor

    I've got the same issue.  In situations where I am just looking at a single column of info I use a pie chart (or other similar) that gives the % for category values.  Same can be done as discussed in this thread via the data label macros.

     

    The gap exists, at least as far as I have seen, when I'm trying to produce a table view.

     

    In the attached screen shot, I have "Industry Product Groups" and need to know for each what % of the total they are so I can then compare the actual results to a goal "IPG Mix Goal".  I heard at Domopalooza that totals for tables is coming in an enhancement, maybe that would include a solution for this as well (fingers crossed). 

     

    I'm trying to leave behind ALL MS Excel reports... this is an example where basic Excel Pivot Table features cannot be replaced in Domo. (in Excel this is "% of Column Total" in value field settings).  Users are having to export to Excel from Domo to then produce the category comparisons.  No bueno...

     

     

  • [Deleted User]
    [Deleted User] Member

    @btm

     

    Any thoughts on penidng release of this feature?

    Thanks!

  • MichaelLeonard
    MichaelLeonard Member

    I second this issue.  I'm relatively new to all this, but I'm struggling with summarizing data as well and a pivot table does what I need so easily.  We really need this type of functionality!

     

    Michael

  • MichaelLeonard
    MichaelLeonard Member

    Hi Everyone,

     

    I have a related question/example.  I have a data set where each row represents either an on-time shipment or a late shipment (data is a 1 for on-time, 0 for late) and the shipments fall into different categories (materials, components, etc) also noted on each row.  There is also a date for each row so you can get to the weekly, monthly on-time-delivery %.

     

    What I've been able to do:

    1. Use the 100% Stacked Bar chart and add the %_PERCENT_OF_CATEGORY to generate this green and red bar graph (see image) with a filter for 1 category.
    2. Generate a pie chart that sums up all the total YTD on-time vs. late shipments (see image).

    What I'd like to do:

    1. Create a "Single Value" or "Filled" or "Progress Bar" chart that displays the % on-time data.  Ideally a "Filled" gauge that has our target on-time-delivery % for a given time period.  Do I need to use beastmode to make this possible?

    Hope you all can help!

     

    Cheers,

     

    Michael

  • swagner
    swagner Contributor

    @DaniBoy this is still an open need for me.  Have not been able to address how I described in my post earlier this year.  The addition of subtotals to tables gives the ability to do a % of total, but the formatting is problematic - especially when you don't have multiple layers of sub-categories.  Notice how the % of total is displayed as a seperate line.  I need to display the percent of total as another column value.  (See attached image)

     

    Have not been able to accomplish with beast mode either.

  • [Deleted User]
    [Deleted User] Member

    Thanks @swagner,

     

    I am going to try and track down an internal Beast Mode guru to have a look at this thread.

    Stay tuned and regards,

     

  • user05507
    user05507 Member

    Hey, I have also same issue. Partly found a solution, I have described it in this topic (https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Summing-up-filtred-values-based-on-criteria/m-p/27783#M3833).

     

    But it's not a full solution so would be glad if you could find a way to calculate this thing ?

     

    Cheers,

    Rafal

  • Steve_Ewing
    Steve_Ewing Member

    Has anyone ever found the answer to this?  I'd like to add a percent of total column to a table card as a beast mode.  

  • user05984
    user05984 Member

    @DaniBoyany luck with this?

  • user04379
    user04379 Member

    Looks like a recent release allows this

     

    https://knowledge.domo.com/Visualize/Adding_Cards_to_Domo/KPI_Cards/Transforming_Data_Using_Beast_Mode/Windowing_Functions_in_Beast_Mode

  • user09742
    user09742 Member

    link is not workint. anyone had any success in beast mode or dataflow.

  • user04379
    user04379 Member

    Try finding a page called Windowing Functions in Beast Mode

Categories