4 Replies Latest reply on Feb 21, 2018 4:49 AM by Zhouyi Zhang

    Calculating Commisions: Problem when mixing aggegrate and non-aggregate with IF and Case calculations

    Pepijn Ernst

      Hi guys,


      I am building a dashboard for our Direct Sales team. They get commissioned on the basis of how much business they generate for us. However, we have a different scheme for both Junior and Senior agents.


      In tableau, I have grouped the agents according to level (Junior and Senior), and I have created two calculations (which work correctly) on an aggregated level for the commissions per month.


      I have tried both things with simple IF and CASE calculations but for both I receive an error regarding the mixing of aggregate and non-aggregate measures.


      The underlying commission calculations are below:

      For Juniors, what it means is that if they reach below a certain amount, they only get a 0.00X%, if sell between two amounts, they get a bit more 0.001X%, and if they sell more than a certain amount they get the max commission. For Seniors, we have a similar scheme but with higher commission proportions.


      I am looking for a calculation that can help me present the Sr. calculations for only the Sr. groups and the Jr. calculations for the Jr. groups (both groups are dimensions, and the caculations are aggregated measures).


      Does anyone have any solutions?