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.
Hi,
I am working with a fairly large set of indicators with different values (target, result) and related dimensions (e.g. year, project, country, donor). Each indicator can have multiple projects reporting on it, their indicators then have unique IDs. The values can be entered by users either incrementally (e.g. services delivered, i.e. total for a selected period equals sum of all values in that period) or as more of an observational status (e.g. number of active members, i.e. total for a selected period might rather be the highest value in that period). I am trying to create a calculation that uses the correct total depending on whether the indicator is incremental or not, evaluated for each distinct ID reporting on the same indicator. This needs to work with different user filter contexts, when drilled down (e.g. per project) and at an aggregated level (e.g. all that submitted data to a particular indicator).
I have tried variations of SUMX, SUMMARIZE, GROUPBY, MAX... I think I have managed to create a calculation to find the max value for a specific indicator ID and then get the sum of all these max values, but I cannot seem to find a way to do this only for indicators that are not incremental, while summing the values of the rest.
Here's an attempt at some sample data:
Indicator | Incremental | ID | Year | Value |
A | Yes | 1 | 2019 | 10 |
A | Yes | 1 | 2020 | 10 |
A | Yes | 2 | 2019 | 20 |
A | Yes | 2 | 2020 | 30 |
B | No | 3 | 2019 | 20 |
B | No | 3 | 2020 | 10 |
B | No | 4 | 2019 | 30 |
B | No | 4 | 2020 | 30 |
Total result per indicator ID for both years should sum values on distinct incremental indicator ID and show the max value on non-incremental indicator IDs. So the result for above sample should then be:
Indicator | Incremental | ID | Total |
A | Yes | 1 | 20 |
A | Yes | 2 | 50 |
B | No | 3 | 20 |
B | No | 4 | 30 |
At a more aggregated level however, the total should sum up values on distinct incremental indicator IDs as before, but also sum the max values of distinct non-incremental indicator IDs:
Indicator | Incremental | Total |
A | Yes | 70 |
B | No | 50 |
Note that I have attempted to get the highest value for non-incremental indicators, but the latest value or the average value might also be interesting (still trying to figure out what will work best here).
I'm also hoping to end up with something that is not too heavy, as this calculations forms the basis for many other measures as well as different visualisations.
Hope the intention was clear. Thanks in advance!
BR
Emma
Solved! Go to Solution.
@Anonymous ,
I don't know all your prerequisites, so not sure if it's the best solution but try this one:
Measure =
VAR curentType = SELECTEDVALUE(T[Incremental])
VAR currentIndicator = SELECTEDVALUE(T[Indicator])
RETURN
IF(curentType = "Yes",
CALCULATE(SUM(T[Value]), T[Indicator] = currentIndicator),
SUMX(
SUMMARIZE(T,T[Indicator],T[Incremental],T[ID]),
CALCULATE(MAX(T[Value])))
)
Did I answer your question? Mark my post as a solution!
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
(Edited after giving it another go!)
Thanks for your suggestion, this is along the lines of what I have been trying - or at least the SUMX part for the non-incremental indicators, but could not figure out how to solve both in one calculation. I got errors with when trying your calculation (too few arguments passed to the SUMX function), but tried to adapt it and I *think* it works (have to do more checks using it in different contexts). Have to admit I do not quite understand SELECTEDVALUE, so will have to read up on that.
Result =
VAR incremental = SELECTEDVALUE(T[Incremental])
RETURN
IF(incremental = "Yes",
CALCULATE(SUM(T[Value])),
SUMX(
SUMMARIZE(
T,
T[Id],
"maxtotal", MAX(T[Value])), [maxtotal]))
Thank you very much for your help!
@Anonymous ,
I don't know all your prerequisites, so not sure if it's the best solution but try this one:
Measure =
VAR curentType = SELECTEDVALUE(T[Incremental])
VAR currentIndicator = SELECTEDVALUE(T[Indicator])
RETURN
IF(curentType = "Yes",
CALCULATE(SUM(T[Value]), T[Indicator] = currentIndicator),
SUMX(
SUMMARIZE(T,T[Indicator],T[Incremental],T[ID]),
CALCULATE(MAX(T[Value])))
)
Did I answer your question? Mark my post as a solution!
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
(Edited after giving it another go!)
Thanks for your suggestion, this is along the lines of what I have been trying - or at least the SUMX part for the non-incremental indicators, but could not figure out how to solve both in one calculation. I got errors with when trying your calculation (too few arguments passed to the SUMX function), but tried to adapt it and I *think* it works (have to do more checks using it in different contexts). Have to admit I do not quite understand SELECTEDVALUE, so will have to read up on that.
Result =
VAR incremental = SELECTEDVALUE(T[Incremental])
RETURN
IF(incremental = "Yes",
CALCULATE(SUM(T[Value])),
SUMX(
SUMMARIZE(
T,
T[Id],
"maxtotal", MAX(T[Value])), [maxtotal]))
Thank you very much for your help!
Hi @Anonymous ,
It seems that your issue have been solved. Would you please try to accept useful reply as answer to help others find it more quickly?
Best Regards,
Dedmon Dai
@Anonymous , I am not clear on the expected output. What you are showing step by step is just sum
Hi,
The total per indicator ID (second table) is not just a sum. It is the sum of all respective ID values for incremental indicators, but the highest reported value for non-incremental indicators. The final table then sums these max values evaluated per ID for non-incremental indicators. Not sure if that made more sense? I find it a bit difficult to explain.
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 |
---|---|
118 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |