how many ids are 75% of the total weight with filter

[Bailo226889]

Hello,
I have an urgent problem please help me.
I would like to have the number of ids representing 75% of the total weight if and only if the Flag = True.
The result should be 2 since the weight 3849 and the weight 2268 make 75% of the total if we remove the first line which has a flag False 

[Bailo226889]

I want the sum of the largest values ​​to be 75%. There is indeed a criterion of decrease.

In this example, 2 Ids are enough (9 and 11) to reach 75% of the total

[AlexisOlson · Accepted solution]

OK. In that case, @tackytechtom's approach is on the right track.

The basic steps are to create a running total (using the ordering criterion) and find how many rows it takes to cross 75% of the total. I'd write it as a measure instead of a calculated column:

CountToThreshold =
VAR _Filtered_ = FILTER ( Table1, Table1[Flag] = TRUE () )
VAR _Ranked_ = ADDCOLUMNS ( _Filtered_, "@Rank", RANKX ( _Filtered_, [NB] ) )
VAR _RunTot_ =
    ADDCOLUMNS (
        _Ranked_,
        "@RunTot", SUMX ( FILTER ( _Ranked_, [@Rank] <= EARLIER ( [@Rank] ) ), [NB] )
    )
VAR _Total = SUMX ( _Filtered_, [NB] )
RETURN
    MINX ( FILTER ( _RunTot_, [@RunTot] >= 0.75 * _Total ), [@Rank] )

[Bailo226889]

Thank you very much for your answer. With a modification of your code I managed to get the result of what I wanted. Do you see please explain your code to me?

[AlexisOlson]

I already gave the high-level logic. Here's a comment version of the specific implementation:

CountToThreshold =
VAR _Filtered_ = /*Remove False flags*/
VAR _Ranked_ = /*Add a rank column to the filtered table based on [NB]*/
VAR _RunTot_ = /*Add a running total column summing [NB] ordered by rank*/
VAR _Total = /*Calculate the [NB] total of the filtered table*/
RETURN
    /*Take the first rank where the running total exceeds 75% of the total*/

[tackytechtom]

Hi @Bailo226889 ,

I think I came to a solution which is somewhat complicated, but it might get you onto the right path anyway:

I created three calculated columns and a measure for explanation purposes. You could of course summarize them if you'd like:

The first column is TomsRank. This column helps us to know in which order we need to sum over the values to check whether we reached the threshold (75%). The ranking here is based on [NB_POID_MSM] in descending order, meaning we will sum up the biggest numbers first. With this, we ensure to calculate the least number of rows needed to get over the threshold.

TomsRank = 
RANKX ( 
    'Table',  
    'Table'[NB_POID_MSM],
    ,
    DESC
)

TomsActionColumn1 calculates the running total over [NB_POID_MSM] where Flag in_MSM = true:

TomsActionColumn1 = 
CALCULATE ( 
    SUM ( 'Table'[NB_POID_MSM] ),
    Table[Flag in_MSM] = True,
    FILTER ('Table', 'Table'[TomsRank] <= EARLIER ( 'Table'[TomsRank] ) )
) 

TomsActionColumn2 first creates a "static" ( --> All ( Table ) ) _TotalWeightTrue variable which is multiplied by 0.75 returning the threshold. We then check for every row whether the running total (TomsActionColumn1) has exceeded this threshold or not. If it has not we return 1 otherwise 0. Also here, we ignore Flag in_MSM = false.

TomsActionColumn2 = 
VAR _TotalWeightTrue = 
CALCULATE ( 
    SUM ( Table[NB_POID_MSM] ),
    Table[Flag in_MSM] = True, 
    ALL ( Table )
) * 0.75
RETURN
IF ( 
    Table[TomsActionColumn1] = BLANK(), 0, 
    IF (
         Table[TomsActionColumn1] <= _TotalWeightTrue, 1, 0
   )
)

The last step is to sum up all values in TomsActionColumn2 and add 1 to the result. The "+1" artificially adds the row needed to get over the 75%. This row is stamped with 0 in TomsActionColumn2 as it's already gone over the threshold.

TomsWeightMeasure = 
SUM ( Table[TomsActionColumn2] ) + 1

Does this help you? 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Bailo226889]

Hello, thank you very much for your answer. It is very clear and of course that I could accept it as an answer. My problem is that I have to do this calculation for several indicators I'm afraid that it will be quite heavy afterwards and that it will cause me performance problems. I was thinking of having a single measure that does all of this at the same time. but if I have no choice by tomorrow, I will take your solution.

Thank you for all the time you spent on my problem, I really appreciate it.

[AlexisOlson]

This isn't necessarily well-defined.

Suppose you have values 10, 15, 20, 25, 30. Is the answer 3 or 4?

10 + 15 + 20 + 30 = 75

20 + 25 + 30 = 75