Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to 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
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
141 | |
120 | |
112 | |
59 | |
59 |