Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Data is mainly indicators and year of publication - table SDG.
I have a table called 'Old' that makes the % of availability change, because it considers only X years old.
On the pbix I upload here I have a page with a table with Country and %.
I need to know:
- Number of countries with % < 25
- Number of countries with % between 25 and 50
- Number of countries with % between 50 and 75
- Number of countries with % > 75
And these numbers will move when the threshold year (Old value) moves, so it has to be a measure.
Can you figure it out? Is it even possible? Please help!
Solved! Go to Solution.
Hey @Anonymous ,
as your measure always needs a filtercontext, you have to create that table internally in the measure and then count the rows with the desired criteria.
The following should work for the 25%:
025 =
-- Creates a table with Geographic area and the % measures
VAR vBaseTable =
ADDCOLUMNS (
VALUES ( SDG[Geographic area] ),
"@%", [%]
)
-- Then you filter that table to the rows where % < 0.25
VAR vFilterdTable = FILTER ( vBaseTable, [@%] < 0.25 )
RETURN
-- And then you sum the amount of rows that are left
SUMX ( vFilterdTable, 1 )
Then you have to add a second filer criteria for the amount 25-50%:
2550 =
VAR vBaseTable =
ADDCOLUMNS (
VALUES ( SDG[Geographic area] ),
"@%", [%]
)
VAR vFilterdTable = FILTER ( vBaseTable, [@%] >= 0.25 && [@%] < 0.5 )
RETURN
SUMX ( vFilterdTable, 1 )
And repeat the same for 50-75:
5075 =
VAR vBaseTable =
ADDCOLUMNS (
VALUES ( SDG[Geographic area] ),
"@%", [%]
)
VAR vFilterdTable = FILTER ( vBaseTable, [@%] >= 0.5 && [@%] < 0.75 )
RETURN
SUMX ( vFilterdTable, 1 )
And last but not least or > 75%:
75 =
VAR vBaseTable =
ADDCOLUMNS (
VALUES ( SDG[Geographic area] ),
"@%", [%]
)
VAR vFilterdTable = FILTER ( vBaseTable, [@%] >= 0.75)
RETURN
SUMX ( vFilterdTable, 1 )
Hey @Anonymous ,
that was close. But you have to add it to the vBaseTable as in the filtered the blanks already won't exist.
The following should work:
025 =
-- Creates a table with Geographic area and the % measures including blanks
VAR vBaseTable =
ADDCOLUMNS (
VALUES ( SDG[Geographic area] ),
"@%", IF([%] <> BLANK(), [%], BLANK())
)
-- Then you filter that table to the rows where % < 0.25 or BLANK
VAR vFilterdTable =
FILTER ( vBaseTable, [@%] < 0.25 || [@%] = BLANK() )
RETURN
-- And then you sum the amount of rows that are left
SUMX ( vFilterdTable, 1 )
Hi @Anonymous ,
Please try to modify your [Count of INDICATOR not older]:
Count of INDICATOR not older = var a=
CALCULATE(DISTINCTCOUNT('SDG'[INDICATOR]), FILTER(SDG,'SDG'[how old] < MAX(Old[Old])+1 )
)
return if(ISBLANK(a),0,a)
Then use the following measure:
Number of countries with % < 25 = var a =SUMMARIZE(SDG,SDG[Geographic area],"_%",[%]) return COUNTX(FILTER(a,[_%]<=0.25),SDG[Geographic area])+0
Number of countries with %between 25 and 50 = var a =SUMMARIZE(SDG,SDG[Geographic area],"_%",[%]) return COUNTX(FILTER(a,[_%]>0.25&&[_%]<=0.5),SDG[Geographic area])+0
Number of countries with %between 50 and 75 = var a =SUMMARIZE(SDG,SDG[Geographic area],"_%",[%]) return COUNTX(FILTER(a,[_%]>=0.5&&[_%]<0.75),SDG[Geographic area])+0
Number of countries with %between >75 = var a =SUMMARIZE(SDG,SDG[Geographic area],"_%",[%]) return COUNTX(FILTER(a,[_%]>=0.75),SDG[Geographic area])+0
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hey @Anonymous ,
as your measure always needs a filtercontext, you have to create that table internally in the measure and then count the rows with the desired criteria.
The following should work for the 25%:
025 =
-- Creates a table with Geographic area and the % measures
VAR vBaseTable =
ADDCOLUMNS (
VALUES ( SDG[Geographic area] ),
"@%", [%]
)
-- Then you filter that table to the rows where % < 0.25
VAR vFilterdTable = FILTER ( vBaseTable, [@%] < 0.25 )
RETURN
-- And then you sum the amount of rows that are left
SUMX ( vFilterdTable, 1 )
Then you have to add a second filer criteria for the amount 25-50%:
2550 =
VAR vBaseTable =
ADDCOLUMNS (
VALUES ( SDG[Geographic area] ),
"@%", [%]
)
VAR vFilterdTable = FILTER ( vBaseTable, [@%] >= 0.25 && [@%] < 0.5 )
RETURN
SUMX ( vFilterdTable, 1 )
And repeat the same for 50-75:
5075 =
VAR vBaseTable =
ADDCOLUMNS (
VALUES ( SDG[Geographic area] ),
"@%", [%]
)
VAR vFilterdTable = FILTER ( vBaseTable, [@%] >= 0.5 && [@%] < 0.75 )
RETURN
SUMX ( vFilterdTable, 1 )
And last but not least or > 75%:
75 =
VAR vBaseTable =
ADDCOLUMNS (
VALUES ( SDG[Geographic area] ),
"@%", [%]
)
VAR vFilterdTable = FILTER ( vBaseTable, [@%] >= 0.75)
RETURN
SUMX ( vFilterdTable, 1 )
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
76 | |
71 | |
56 | |
39 | |
35 |
User | Count |
---|---|
66 | |
66 | |
59 | |
53 | |
45 |