Replicate Excel Formula by using Domo to Calculate 80/20

leeloo_dallas

Hey there! I'm trying to apply the Pareto Principle to the following dataset I've attached using either Beast Mode, Magic ETL, or MySQL.

I want to replicate what is done in column "Bottom 25" in Domo but I'm at a loss.
I've used the following query, but it gives me whole numbers instead of what's displayed in the "Bottom 25" Column.

In the second image is a screenshot of what the following query provides me. 

Using the Magic ETL, I have already created a new column called "users total" that is a constant sum of the "Users" column.

 

SELECT 
a.*,
round(100*(SELECT COUNT(*) FROM `copy_of_test_xlsx` AS b WHERE b.`Users` <= a.`Users` ) / total.cnt,1 )
AS percentile FROM `copy_of_test_xlsx` AS a 
CROSS JOIN (
  SELECT COUNT(*) AS cnt
  FROM `copy_of_test_xlsx`) AS total
ORDER BY percentile DESC

 

The Bottom 25 columns was found by using the following:
"Audience Interest Score" =C2/(SUM(C:C))

"Bottom 25" =SUM(D$2:D2)

 

Is there any way to replicate this? Please and thank youuu

GrantSmith

Hi @user052846 

 

I understand now. You're simply doing a running total of the `Audience Interest Score` column. You can do this with a window function within a beast mode however you'd need to make sure you have windowing functions enabled in your instance. You can talk with your CSM to get them turned on. It's a simple feature switch.

 

SUM(SUM(`Audience Interest Score`)) OVER (ORDER BY `Audience Interest Score` DESC)

 

GrantSmith

Hi @user052846 

 

I'm a little confused by your request as the only whole number you have is the number of users. Looking at your two examples you have a decimal representation of a percentage (Domo) compared to your excel document (whole # representation). In cards you can tell Domo to display as a percentage (and tell it to multiply by 100) by clicking on the column name on your table card (assuming you're using a table card to replicate the excel document) selecting Format > Display As > Percentage and check the box that says multiple by 100. This way you don't need to calculate the percentage yourself.

Alternatively you could format your Bottom 25 column as a percentage in your SQL [ ROUND(Bottom25 * 100, 2) ] however I'd recommend against this option as it will remove data precision and doesn't allow you to change how many decimal places are utilized)

 

If you're wanting to do the conditional text formatting to highlight rows with values over 75% like your excel document you'll want to make sure you use the decimal representation (0.75 instead of 75) since that is how the data is actually represented in your dataset.

leeloo_dallas

Oh sorry I didn't clarify.
From the SQL query I added, I was wondering if there's another way to run the query so that the percentile column I created in DOMO shares the same numbers as displayed in the Bottom 25 column in excel. 

GrantSmith

Hi @user052846 

 

I understand now. You're simply doing a running total of the `Audience Interest Score` column. You can do this with a window function within a beast mode however you'd need to make sure you have windowing functions enabled in your instance. You can talk with your CSM to get them turned on. It's a simple feature switch.

 

SUM(SUM(`Audience Interest Score`)) OVER (ORDER BY `Audience Interest Score` DESC)

 

leeloo_dallas

Wow, it's so simple ?

Thanks so much! I really appreciate your help!