Payer Mix Table instead of Pie Chart

[wrprinty]

I have data for revenue by baseline period.  That revenue is linked to various payers. I'm looking for a table equivalent to the calculation that happens with a pie chart.

The expression: 

Sum({$<Baseline={'FY20 Budget'},Class={'Revenue'}>}Amount) 

yields the correct dollar amount by payer and restricts the calculated amount according to Payer selection - as expected.

and the expression:

Sum({1<Baseline={'FY20 Budget'},Class={'Revenue'}>}Amount)

yields the correct dollar amount by payer but does NOT restrict the calculated amount according to Payer selection - as expected.

HOWEVER, the expression:

Sum({$<Baseline={'FY20 Budget'},Class={'Revenue'}>}Amount) /Sum({1<Baseline={'FY20 Budget'},Class={'Revenue'}>}Amount)

always yields 100%, not the respective payer mix as it does in a pie chart.

I would appreciate any suggestions.  Thanks.

[hallquist_nate]

Well, my guess is that you are always getting 100% is becuase you are simply dividing the whole by the whole.  For example, if your total is 200, then your expression yields 200/200, becuase you haven't actually dimensionalized the expression.  

Sum({$<Baseline={'FY20 Budget'},Class={'Revenue'}>}Amount)  (=200)

/

Sum({1<Baseline={'FY20 Budget'},Class={'Revenue'}>}Amount) (=200)

I don't have any data to really work with on this, but you might want to try the AGGR() function to see if you can get the numerator to calculate by the dimension, and then the denominator will remain the same.  Another way to try this is by creating a column in a straight table for each expression and then using a column reference formula.  I find that if I break an expression out into it's pieces in a table, I can see how the expressions are executing against my expectation, and that will lead me to inspiration on a solution.

Good Luck,

Nate