subtotal to sum up associated values

[Thomas_San]

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!

[v-shamiliv]

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.

[v-shamiliv]

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

[v-shamiliv]

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.

[maruthisp · Accepted solution]

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

[johnt75]

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
)