Count rows with a valid flag based on total running percentage of grand total into Matrix

[MattiaMaetini]

Hi all,

I have a measure that produce a Running Total (and it's percentage of total-year spending) row by row (Providers, first column of image) and it works correctly.

Now, starting from it, I would like to create a new measure that give me the count of rows where "check < 80" is equal to 1.

Below the code and my comments:

Check<80% =
VAR CurrentRank = [Ranking]

VAR RankAndOrders =
ADDCOLUMNS (
ALLSELECTED(FORNITORI_D_ANAGFORNITOREAGGREGATO[FornitoreAggregato]),
"@Ranking", [Ranking],
"@Spending", [FORNITORIF_Spending_Fornitore_ANNO_M€]
)

--> here I prepare my data for all my providers

VAR BetterCategory =
FILTER (
RankAndOrders,
[@Ranking] <= CurrentRank
)

VAR BetterCategory_RT =
ADDCOLUMNS(BetterCategory, "Perc_CUM", DIVIDE(SUMX ( BetterCategory, [@Spending] ), [FORNITORIF_Spending_TOTALE_ANNO_M€]))

--> calculate running Total % foreach Provider

RETURN
IF(DIVIDE(SUMX ( BetterCategory, [@Spending] ), [FORNITORIF_Spending_TOTALE_ANNO_M€])<0.8, 1, 0)

--> this code running perfectly into Matrix row context: how could I translate it for obtain the SUM of ROWS where Flag=1?

I just tried with:

RETURN
CALCULATE(SUMX(BetterCategory_RT_Flag, [@Spending])) 

but result is always equal to 1.

(with COUNTROWS result is always 1 too).

Could you help me to achieve this result? (43 instead 1):

 BR,

M

[Anonymous · Accepted solution]

Hi @MattiaMaetini ,
Based on your description, I created some test data

Code	Value
1	7
2	8
3	9
4	6
5	4

Create two measures

Flag = 
VAR _percentage = DIVIDE(SELECTEDVALUE('Table'[Value]), 10)
RETURN
IF(
    _percentage > 0.6,
    1,
    0
)

Count of flag = 
SUMX(
    ADDCOLUMNS(
        ALLSELECTED('Table'[Value]),
        "Flag",[Flag]
    ),
    [Flag]
)

Final output

In your code above, first keep the MEASURE of the judgment flag, and then ceate the following

Count of Check<80% = 
SUMX(
    ADDCOLUMNS(
        ALLSELECTED(FORNITORI_D_ANAGFORNITOREAGGREGATO[FornitoreAggregato]),
        "CheckFlag", [Check<80% Flag]
    ),
    [CheckFlag]
)

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly