Grand total for a conditional measure

[AnthonyXelya]

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 > 120

I would like to have in the "Total" line the sum of the column, instead of that the conditions are applied to the total.

I have tried the column "Measure" (on the Sim B) like that : 

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

I would like to have 18 in the total for Simulation A and 55 in the column for Simulation B :

Need help!

[MFelix]

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. 

[Anonymous · Accepted solution]

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