Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHello
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |