topic Re: Need DAX for Total Summing Subtotal instead of Calculating Total in DAX Commands and Tips

Need DAX for Total Summing Subtotal instead of Calculating Total

etane — Wed, 06 Nov 2024 23:53:48 GMT

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.

Re: Need DAX for Total Summing Subtotal instead of Calculating Total

Anonymous — Thu, 07 Nov 2024 01:42:56 GMT

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.

Re: Need DAX for Total Summing Subtotal instead of Calculating Total

etane — Thu, 07 Nov 2024 17:36:44 GMT

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.

Re: Need DAX for Total Summing Subtotal instead of Calculating Total

Anonymous — Fri, 08 Nov 2024 08:42:02 GMT

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.