Ratio of aggregated columns?

[ai52487963]

Hi all, I'm trying to take the ratio between two aggregated columns, but not sure how to proceed.

The table of data I have looks like this, where my date is binned in 7 day groups from the raw source, the "Hits" and "Creation_Attempts" columns are both aggregate sums, and the "Average of Create_To_View_" is ...well... an average:

You'll notice that the ratio of 31920/323658 is not actually 0.03 but closer to 0.09. The value 0.03 is being displayed because it's calculating the average of the ratio across the raw data instead of my binned aggregate values. 

Is there any way outside of running some custom R code of getting the =DIVIDE() function to run against these aggregated columns here instead of the raw data and having the average displayed? I want the 0.09 instead of the 0.03.

[dbk58]

I am having the same issue here. The sum (or avg, or whatever) of the aggregates is defintely not the same as the aggregates of the sums.

It's exasperating. I can see the two values for which I want to compute a ratio summarized neatly at the top of the report. But when I create a calculated column (or measure) using sum(x)/sum(y), I get a wildly inaccurate value. Same with sumx, averagex. 

Should it not be possible to set a calculation to "before/after" aggregation? That would solve my problem. 

In the meantime, if anyone has cracked this nut I'd love to hear about it.

[dbk58]

Actually, with a little trial and error, I managed to figure this out.

I have detal tables and created a summary table based on that. nI'm tracking hours spent on a project vs hours budgeted. If an activity is not budgeted, then we assume it's running at 100%.

For my rollup of ratios, I used:

% Expended Total = 

DIVIDE(
  SUM('ProjSummary'[Actual Hrs]),
  SUM('ProjSummary'[Curr Budg]),
  1
)

[Eric_Zhang]

---

@ai52487963 wrote:

Hi all, I'm trying to take the ratio between two aggregated columns, but not sure how to proceed.

The table of data I have looks like this, where my date is binned in 7 day groups from the raw source, the "Hits" and "Creation_Attempts" columns are both aggregate sums, and the "Average of Create_To_View_" is ...well... an average:

You'll notice that the ratio of 31920/323658 is not actually 0.03 but closer to 0.09. The value 0.03 is being displayed because it's calculating the average of the ratio across the raw data instead of my binned aggregate values. 

Is there any way outside of running some custom R code of getting the =DIVIDE() function to run against these aggregated columns here instead of the raw data and having the average displayed? I want the 0.09 instead of the 0.03.

---

@ai52487963

What are the Hits and Creation_Attempts measures like? Ideally you will get the 0.09 simply by create a measure like 

Average of Create_To_View_ration = DIVIDE([Creation_Attempts], [Hits])

If it doesn't work, please post more "raw" sample data, instead of the aggregated table visual.