Need DAX for Total Summing Subtotal instead of Calculating Total

[etane]

Hello.

So, I am trying to use DAX to calculate Bonus in a Matrix table.  Bonus is Factor x $1,000.  And, Factor is Sales divided by $10,000 rounded down.  In example below, Factor should be 6 at the Salesperson level.  However, at the manager level, 7 is calculated.  But, I need the sum to be calculated at the Salesperson level to multiple against the Bonus amount which is 6 x $1,000 = $6,000.

I think I need a new DAX for factor, but I don't know how to write a DAX that doesn't take the Manager level sum into consideration.  Please help me write one.

Thanks in advance.

[Anonymous]

Hi @etane ,

Based on your description, I created these data.

You can try to create these measures.

Factor = 
ROUNDDOWN(SUM('Table'[Sales]) / 10000, 0)

Factor1 = 
SUMX(ALLEXCEPT('Table','Table'[Salesperson]),[Factor])

Bonus = 
[Factor1] * 1000

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[etane]

Hi Clara.

Is there a DAX that would remove the Manager but keep everything else instead of keeping only the Salesperson but removing everything else?  I tried using Removefilter but maybe I am not applying it correctly.  Unsaid in the opening post, there are other filters applied to this table, so I don't want to remove these filters in the DAX.

[Anonymous]

Hi @etane ,

I don't really understand your needs very well. Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.