COUNT GROUPED SERIES WITH MORE THAN X VALUES, QUARTILE

[Anonymous]

Oh hi! I'm new here.

I'm trying to create a measure that counts the quantity of series with more than X% of the whole seleted range, intenting to bring the data to pie charts showing the "quarterized" data. For example:

A
B
B
B
D
C
A
A
A
A

A = 50%
B = 30%
C = 10%
D = 10%
(how i'd like to return)

I can't(as i know) bring this calcule as a measure column because full table is not grouped yet(it needs to be grouped before calculation, based on the selected range) or calculed table(groupby table for example, because if i change de selected range woudn't relate to the calculated table, so the quarterizing would be static to the full table range). So i thought using COUNTX the grouped measure like:

Measure = COUNTX(GROUPBY(FULL_BASE,FULL_BASE[SERIES],"Count_Column",COUNTX(CURRENTGROUP(),COUNTA(FULL_BASE[SERIES]))),COUNTA([Count_Column]))

(of corse, after that i'd like to bring that measure inside a SWITCH measure to return the flags like 'if counta([Count_Column])>50,">50",counta([Count_Column])>200,">200"... (and so on))
*Another observation, wherever i use the expression criteria from the countx as a comparison, like COUNTA([Count_Colum])>X it return an error. The measure as it is is returning the whole count of series, in the A, B, C, D example, it would return 4.

Also tried:

Measure = SWITCH(ALL(FULL_TABLE[Series]),COUNTX(GROUPBY(FULL_TABLE,FULL_TABLE[Series],"Pedidos",COUNTX(CURRENTGROUP(),COUNTA(FULL_TABLE[Series]))),COUNTA([Count_Column])),"<50",COUNTX(GROUPBY(FULL_TABLE,FULL_TABLE[Series],"Pedidos",COUNTX(CURRENTGROUP(),COUNTA(FULL_TABLE[Series]))),COUNTA([Count_Column]))>1,">50","")

Error returns.

Thx already!

[lbendlin]

Please add some more sample data (more columns) that correspond to your description of the filters you intend to apply.  Please show the expected outcome when applying a filter.

[Anonymous]

I should outcome like this: 

Where: Grey table = full table, Black table = table visual outcome, and pie chart outcome with %s. Blue table is only a count to be clear how it is calculating. It should also change the outcome calcules wherever i select other dates(months in the example), for example, selecting fev should look like this:

[v-stephen-msft]

Hi @Anonymous ,

I created 4 measures.

Count = CALCULATE(COUNT('Table'[Letter]),FILTER(ALLSELECTED('Table'),[Letter]=MAX('Table'[Letter])))

>2 = CALCULATE(DISTINCTCOUNT('Table'[Letter]),FILTER('Table',[Count]>2))

equal 2 = CALCULATE(DISTINCTCOUNT('Table'[Letter]),FILTER('Table',[Count]=2))

<2 = CALCULATE(DISTINCTCOUNT('Table'[Letter]),FILTER('Table',[Count]<2))

Here's the result.

You can find more details from my attachment.

Best Regards,

Stephen Tao

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

[Anonymous]

Oh Hi Stephen! 

Thx  much! I actually didn't think to quarterize de data in multiple variables, instead i was trying to bring every quartile in the same measure, but yes it solves the problem same way. Again, thx much! 

I just have one more question about this "method" you used: 

Why whereas i click the measure in the table measures or the pie chart measures, it doest filter the whole table? 

for example, there i click the "equal 2" and it actually didn't filter the month/letter/count table, by only those with count = 2. Do you know why that happen? 

Observation: I've already solved this problem by creating a switch measure that count the whole tabe and considerates if a auxiliar table(not related) is filtered, or not and what is filtered if it is. If not filtered, it returns the whole table(actually it returns a "applied" value for all the series count, and i include a filter in that measure that filter only what is "applied"), whereas the auxiliar table is filtered, it first considerate the unic value that is filtered, for example, ">2", if so, it only returns the "applied" value for those it calculates more than 2, and so on for the other quartiles. As screenshot and file shows: 

(i tried to indexate the file with the relational measure and aux table here but this shows 

)
Anyway: Heres more screenshots so it can be clear without the file:

Relational_Measure =

var countvar = COUNTA('Table'[Letter])

var filtered = ISFILTERED(Aux_table)

var more_than_2 = IF(countvar>2,"applied","")

var equal_2 = IF(countvar=2,"applied","")

var less_than_2 = IF(countvar<2,"applied","")

var filtered_value_aux_table = MIN(Aux_table[Flags])

return IF(filtered=false,"applied",SWITCH(filtered_value_aux_table,">2",more_than_2,"equal 2",equal_2,"<2",less_than_2))

[v-stephen-msft]

Hi @Anonymous ,

Because "equal 2", ">2" and "<2" are measures, not fields, so there will be no filtering meaning.

Best Regards,

Stephen Tao

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

[Anonymous]

And would there be a way for in visual filtering?

[lbendlin]

Please add some more sample data (more columns) that correspond to your description of the filters you intend to apply.  If you paste the data into a table in your post or use one of the file services it will be easier to work with. Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Anonymous]

Thanks for replying Ibendlin, but that's in fact the only data i need for as you can see in the screenshots, i could have the black table and the pie chart with the full table(grey) outcome. 

These in fact are the only dimensions i need, date column(wich will be filtered) and series column to be count be each different serie.

That result for january for example is just the count of how times per letter appers.

A, 5 times.

B, 2 times.

C, 1 times.

Than, after that, i expect to split them series by range. In the example

>2: 1(A)

=2: 1(B)

<2: 1(C)

Again, as i filter through the months, i expect the outcome to change its values considerating the filtered month. Thx again for keep replying :D! 

[lbendlin]

Your screenshot is not usable. Please post the sample data in usable form.