Aggr Function Grain Mismatch

[#1]

Hello,

I'm trying to fix what I believe is a grain mismatch in an aggr function.

i have a chart with 2 dimensions, and as labels I'm trying to include

     the value (count of transactions)

      same value as a % of the primary dimension's count of transactions

I'm using a dual() function, but struggling to get the % to display.

More specifically, I'm having a hard time getting the denominator (count of transactions across my primary dimension)

I'm using the formula

sum(aggr(DISTINCT Count({<[qBV.Restriction Type]-={''}>}[qBV.Case Number]), [qBV.Primary Payer]))

where [qBV.Primary Payer] is the primary dimension. The secondary Dim is [qBV.Restriction Type].

But due to the grain mismatch, it's only displaying the percentage on one column.

Does anyone know how to adjust my work so that the denominator is calculated for each column?

I'm probably overlooking something simple, but I've tried several different versions of the expression with no success.

Your help is much appreciated!

[jonathandienst]

If you do not have all the chart dimensions in the Aggr() function, it does not produce output for the all the chart columns, so you will only get results in one column.

You need to add both chart dimensions to the Aggr(). This could change the output, so you may need to add a partial total back to correct this.

BYW I see a DISTINCT for your Sum. Should the DISTINCT not be for the Count?

[#3]

Thanks for the response!

Good eye... The DISTINCT was just a left over from my testing; i forgot to get it out of there before posting the expression.

Do you happen to have any suggestions on how to add the partial total back?

Sorry, I'm just not understanding how to get it to work (without hard coding the values of my second dimension into the expression's set analysis).

The two legend values shown aren't the only possible values for the dimension. I could probably get away with making them that way, but I'd really like the chart to be more flexible.

When I add both dimensions to my chart, it just returns the values of the individual bars, making my %s = 100%

Thanks again!

[jonathandienst · Accepted solution]

Possibly like this:

Sum(Aggr(Count({<[qBV.Restriction Type]-={''}>} TOTAL<[qBV.Restriction Type]> [qBV.Case Number]), [qBV.Primary Payer], [qBV.Restriction Type]))

or

Sum(Aggr(Count({<[qBV.Restriction Type]-={''}>} TOTAL<[qBV.Primary Payer]> [qBV.Case Number]), [qBV.Primary Payer], [qBV.Restriction Type]))

(I moved the DISTINCT to the count. If you dont need the distinct count, then remove it.)

[#5]

The second one worked as I had hoped... thank you so much for your help!

Now I understand what you meant by adding the partial total back.

Thanks again!