Hello. I'm trying to make a Pareto chart on metabase and I would like to set the cumulative count as a percentage. I also found the related issue #12017 btw.
I was able to make one with absolute values as follows.
Then, to use percentage values I would need the total count of the query instead of the "group by" counts. In the documentation I found out that I could do that either with a saved question or with a common table expresssion.
The problem with that is converting a question to SQL breaks the cumulative count (#3110). It (the green line data) becomes identical to the simple count (the blue bars) instead of being cumulative.
Is there any other way to do this?
Nice investigation you did - and for links to the issues and documentation.
You would have to create the SQL query manually with cumulative/windowing functions. I don't know which database you're using, but you could probably find an example for your database on stackoverflow.com
Which database are you querying?
Oh, I forgot to mention it's Postgres. From #3110 I had the impression that the cumulative sum breaks because it's not be feasible with SQL.
@bwdm No, it's just because CumulativeCount/Sum was implemented in a middle-layer in Metabase, since it was created before a lot of databases supported windowing - and the "Convert query to SQL" does not include the SQL for that, so it "breaks" when you run the SQL, since it doesn't have any cumulative calculations.
We want to support windowing, so we could implement cumulative directly in the query, but that's a lot of work, since we support 15+ different databases with multiple versions each.
But if you write your own SQL query, then you can do windowing there. Basically anything you write is SQL is send to your database and Metabase just shows the results of that.
This should get you rolling - if not, then post your query: