Forum Discussion

AnthonyXelya's avatar
AnthonyXelya
Icon for Helper II rankHelper II
5 years ago
Solved

Grand total for a conditional measure

Hello,   I have a matrix with one line par client, with 2 conditional columns : - Simulation A : nbUsers * 6, if number of minutes per user per month <= 120 - Simulation B : nbUsers * 11, if > 12...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hello

    When using SUMX() an iterator is used and the context transition starts. The context transition allows you to transform the row context into a filter context, which basically means calculating the desired value for the row you are iterating (clients are iterated here).


    But for the context transition to work, you must use a measure or a CALCULATE():

    - here for [NbOfMinutesPerUserPerMonth] is fine as it is a measure

    - however, for DISTINCTCOUNT(UserAccount[Id]), for each iteration (so for each customer) it will calculate the total number of different accounts in the current context

    > Try adding a CALCULATE around DISTINCTCOUNT:

    IF (
    HASONEFILTER(Client[Name]),
    [SimulationB],
    SUMX( 
        FILTER( Client,
                [NbMinutesPerUserPerMonth]> 120),
        CALCULATE( DISTINCTCOUNT(UserAccount[Id]) * 11) )
    )
)

    Also, I'm not sure that the IF/HASONEFILTER is useful here. Maybe you could just write:

    SUMX(
   FILTER(
      Client,
      [NbMinutesPerUserPerMonth]> 120
   ),
   CALCULATE( DISTINCTCOUNT(UserAccount[Id]) * 11)) )
)

    I hope it works and helps. Really?

    Thomas

MFelix's avatar
MFelix
Icon for Super User rankSuper User
5 years ago

Hi AnthonyXelya ,

 

This type of calculation are context sensitive so you may need to create a temporary table to make the SUMX.

 

However the looking at the code you are placing try the following code:

IF (HASONEFILTER(Client[Name]),
[SimulationB],
 SUMX(ALLSELECTED(Client[Name]), [SimulationB])

 

If this does not work are you abble to share some mockup information so as I refer a temporary table can be used for the context of your measure.