cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HoracioRek
Frequent Visitor

Calculate Count using a measure that uses moving parameter

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!

 

Pbix File link

Capture.JPG

1 ACCEPTED SOLUTION
selimovd
Super User
Super User

Hey @HoracioRek ,

 

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 )

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

View solution in original post

5 REPLIES 5
HoracioRek
Frequent Visitor

Thanks for your solutions! I appreciate it!

How would you do to count the empty ones on the 0-25 as well? @selimovd @FrankAT 

I tried to add:

VAR vFilterdTable = FILTER ( vBaseTable, or([@%] < 0.25, ISBLANK([@%]) )) but it does not seem to work...

Hey @HoracioRek ,

 

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 )

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Hi @HoracioRek ,

 

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

FrankAT
Community Champion
Community Champion

Hi @HoracioRek ,

you can do it with a disconnected table which holds the buckets:

 

10-07-_2021_20-23-16.png

 

The report view looks like this:

 

10-07-_2021_20-21-58.png

 

Take a look at the attached PBIX file.

 

selimovd
Super User
Super User

Hey @HoracioRek ,

 

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 )

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors