topic subtotal to sum up associated values in DAX Commands and Tips

subtotal to sum up associated values

Thomas_San — Tue, 13 May 2025 16:31:44 GMT

Hey everyone,

I would need some help in having subtotals sum up all the associated values as they are. Here is what I mean:

As you can see, my current Measure Target works by multiplying all the Sum of Target values with their respecitve Sum of Sales values. This is fine when it stays at the customer level (such as with customer 83 where 6.798.741€ * (1+0,03) = 7.002.703). However, my issue is that at the Customer Group level, it does exactly the same operation which, obivously, does not make any sense. Here, I would like to have the subtotal at each Customer Group level to be the sum of all Target values below. Hence, rather than having 183.303.557 for the Customer Group Retail, I would like to have 149.397.251 (=49447473 + 35982154 + 55502105 + 2861396 + 5604123).

The Measure Target looks as follows:

Target = var sales= sum(Sales[Sales]) var cal= CALCULATE( sales * (1+sum(Customers[Target])) ) return cal

My data model is simply a customer table that includes the target per customer and a sales table with the actual sales and a FK linking it to the PK of the customer table.

Can anyone therefore please point me to the right direction on how I can get the subtotal figure to behave in the way as described above? Or is this not so much a DAX task and more question of visuals settings?

If it it something that can be addressed with DAX, I am currently thinking about adding a

Target = var sales= sum(Sales[Sales]) var cal= CALCULATE( sales * (1+sum(Customers[Target])) ) var swi= SWITCH( TRUE(), ISINSCOPE(Customers[CustomerID]), cal, ISINSCOPE(Customers[CustomerGroup]), ???????? ) return swi

to the DAX above. But I am not sure what to put in for ????????.

Thanks in advance!

Re: subtotal to sum up associated values

johnt75 — Wed, 14 May 2025 15:41:22 GMT

I think this will work at any level of the hierarchy

Target = SUMX ( Customer, VAR sales = CALCULATE ( SUM ( Sales[Sales] ) ) VAR cal = sales * ( 1 + Customers[Target] ) RETURN cal )

Re: subtotal to sum up associated values

maruthisp — Thu, 15 May 2025 04:53:28 GMT

Hi Thomas_San,

I guess, your DAX expression does work at the customer scope but not at group scope.
Try below DAX:
Target =
SUMX(
VALUES( Customers[CustomerID] ), // ← iterate each customer in current filter context
CALCULATE(
SUM( Sales[SalesAmt] ) * // ← that customer’s sales
( 1 + SUM( Customers[TargetPct] ) ) // ← times 1 + that customer’s target%
)
)

I am not sure, how data looks from your side as you haven't provided sample data to check the DAX expression.
It would be good to share sample data for testing the DAX expression.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks!

Best Regards,

Maruthi

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/

X - Maruthi Siva Prasad - (@MaruthiSP) / X

Re: subtotal to sum up associated values

Anonymous — Thu, 22 May 2025 12:52:59 GMT

Hi Thomas_San

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

Re: subtotal to sum up associated values

Anonymous — Mon, 26 May 2025 12:25:57 GMT

Hi Thomas_San
I wanted to check if you had the opportunity to review the information provided by maruthisp and johnt75 . Please feel free to contact us if you have any further questions. If their response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you

Re: subtotal to sum up associated values

Anonymous — Fri, 30 May 2025 10:34:41 GMT

Hi Thomas_San
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.