Hi there,
My goal is to have a measure, specifically an average, calculate across the entire underlying data set per row when I have pivoted columns.
I can achieve this result if I check “Row Totals”. Additionally, I can do this with sum and pivot_row for number measures. However, I would like to add additional customization if needed so I need this in a table calc. Below is an example screenshot.
Any thoughts?
Hi
We can get the number of columns with max(pivot_row(pivot_column()) , so then we end up with
which looks like this:
Is this what you were looking for ? If not, let me know!
Hi Peggy, appreciate the help. To clarify the “Count” and “Avg Days” are two separate measures calculating completely different metrics.
My goal is to have the “Avg Days” measure calculated across the underlying data set per row, not the values you see visually in the table itself.
More like a “Total Avg” of “Avg Days”
Hi
Looks like you’re looking to compute a “weighted average”. I took the count divided by the total row count as the weight, multiplied it by the average, and computed the overall weighted average using the sum of the weighted average / sum of the weights
I used table calcs to assign the values from your example, and hid some from the visualization. I also did a mean of the average just to show how it differs.
Does this
Hi Peggy, thanks again for your support.
I’m simply looking for the Row Total Average OF the Avg Days Measure.
The additional “Count” column may be confusing as it is not considered in the calculation I’m after. I just so happen to want to the see the Count by State and Avg Days by State for analysis.
I’m just looking for the equivalent of checking the “Rows” checkmark box but in a Table Calc formula instead. In your example, I would want to see the Avg Days for Orders created on 12-8 Created Date across ALL Order Statuses.
Thanks!
OK 🙂 If I’m understanding this right, that’s the mean of the Avg Days across each row.
Like this: mean(pivot_row(${avg_days})) . That comes out to 11.5 / 3 in your example, or 3.83
The 4.5 value from your post is what I thought we were going for, which we can only get if we either take the average across all of the values used to create the Avg Days aggregate measure, or do a weighted average.
Any value that you want to use in a table calculation must be in the results table.
Got it thanks Peggy, I may have initially misunderstood the math behind “mean of weighted average”.
I was thinking there would be a simpler calculation approach more akin to DAX in PowerBI.
Appreciate the help!
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
