Sum or max of values in group based on column condition

[Anonymous]

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

[ERD · Accepted 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! 

[ERD · Accepted 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! 

[Anonymous · Accepted solution]

(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!

[v-deddai1-msft]

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

[amitchandak]

@Anonymous , I am not clear on the expected output. What you are showing step by step is just sum

[Anonymous]

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.