Aggregating in a Pivot Table with unique dimension Calculations

brittsch1 — Thu, 14 Jul 2022 14:17:04 GMT

Hi, I am trying to get a calculated value for a pivot table with four tiers.

In Dimension = 4, the data is working perfectly, but I can't get it to work on the higher dimensions.

My Data looks something like this:

Month > Program > Clinician > Client

or Dim 1 > Dim 2 > Dim 3 > Dim 4

Each client needs four contacts per month, some will have more, others less. Since i want the max percentage as 100% i need to make sure that nothing exceeds 4 contacts per month.

The current equation for Percent of Sessions Held is:

if(Dimensionality() = 4
,if(column(2)<Count([Client]),column(2)/column(2),Count([Client])/column(2))
,if(Count([Client])>column(2),column(2)/column(2),Count([Client])/column(2)))

with Column(2) being:

if(Dimensionality() = 4,[Required_Services],count(distinct [Client])*[Required_Services])

I've created a numerator column here to test values, but again can only get the calculation for Dimension 4 (Client)

I would like Dimension 4 to be summed up to provide the totals for Dimensions 1-3.

I've tried various aggregation equations with no luck

aggr(sum(Dimension 4),Client, Clinician)

Re: Aggregating in a Pivot Table with unique dimension Calculations

Or — Thu, 14 Jul 2022 14:35:14 GMT

I had a bit of an issue following along with the entire thing, but in general, it seems like what you'd need is something along the lines of:

Avg(Aggr(RangeMin(Count([Number of Sessions])/Only([Required Services]),1),Month,Program,Clinician,Client))

Which would translate to the average (at whichever level this appears in) of each individual combination of % score (capped at 1 by RangeMin) for each combination of Month, Program, Clinician, and Client.

topic Aggregating in a Pivot Table with unique dimension Calculations in Reporting Service & Alerting

Aggregating in a Pivot Table with unique dimension Calculations

Re: Aggregating in a Pivot Table with unique dimension Calculations