sum based on distinct in another column

WizardOz
WizardOz Contributor
in Magic ETL

hello, this seems to be straight forward, but I could not get it.

 

I wanted to sum up the cost based on distinct code, but my beast mode does nto work:

a sample data, I wanted to calculate sum of cost by distinct code, the sum should be 33.  each code has the same cost.  

 

this is my beast mode: 

SUM(
CASE
WHEN(distinct `code') then `cost`
END
)

 

codecost
110
210
35
110
48

 

Best Answer

  • WizardOz
    WizardOz Contributor
    Answer ✓

    I added an indicator in the dataflow.  Every code when it is the first time presneted in the data, the indicator is 1, then I sumed up based on this indicator.

     

    sum(
    case
    when `code_indicator` = 1 then `mc_spend`
    else 0
    end
    )

Answers

  • guitarhero23
    guitarhero23 Contributor

    Is something stopping you from just displaying the SUM of Cost by Code on a chart since normally in Domo if you were to put Code and then SUM of Cost on one card it would already do what you're asking without a beastmode.

     

    Dojo 2.JPG


    **Make sure to like any users posts that helped you and accept the ones who solved your issue.**
  • WizardOz
    WizardOz Contributor

    As you can see sum in the table has code 1 sumed up as 20, so I will get total of 43.  But the actual sum is 33, in other words, I only want to sum for each code once.  

     

    in adition, after I get the sum, I will do some other calculations such  as cost per visit, cost per download etc.

  • ST_Superman
    ST_Superman Domo Employee

    This isn't something that you can do within a beastmode calculation.  Especially if you are going to perform further calculations on it.  I would recommend creating a new data set where you agregate this data at different levels.  (once aggregated by visit id, once by download id, etc.)  

     

    You could use 

    SUM(DISTINCT `Cost`)

    but that will only work to get the correct value for each row in the table, the Total row will still be off:1.png

     

  • WizardOz
    WizardOz Contributor

    sum (distinct 'cost' ) could not give me the correct sum either.  My dataset has 70m rows and 200 columns, and it is updated daily, I have many other calcualtions (combinations)  I would like to do.  I hope I can do it in beast mode instead of in dataflow.

  • WizardOz
    WizardOz Contributor
    Answer ✓

    I added an indicator in the dataflow.  Every code when it is the first time presneted in the data, the indicator is 1, then I sumed up based on this indicator.

     

    sum(
    case
    when `code_indicator` = 1 then `mc_spend`
    else 0
    end
    )

  • brandon_johnson
    brandon_johnson Member

    I love your solution @WizardOz ! Simple, but allows for the data to remain unaggregated at a dataset level, that way you can aggregate in the card and employ drill path to get more and more detail, all from the same dataset! Wish I could like your answer more than once!

Categories