Dynamic percentage calculation on subtotals

[Syndicate_Admin]

Good afternoon

I am trying to assemble a board with the information of the main characteristics of a population group. The information must be mounted on cards and this must be filtered by each of the regions from where the data were obtained.

*First use a measure to count the information:

Count = COUNTROWS(Undergraduate)

*Second, use another measure to count the total information:

Total count = COUNTROWS(ALL(Undergraduate))

*Al final divided both measures:

% = DIVIDE([Count],[Total Count])

When I assemble the information without filters selecting the most relevant characteristics, the result obtained is the desired one. However, when I filter the information, the calculation of the percentage is made on all the data and what I am looking for is that this calculation takes as the new 100% the data only from the filtered region.

I ask for your help to guide me and tell me what alternatives I have to find the solution to this difficulty.

[v-yueyunzh-msft]

Hi, @Syndicate_Admin 

You can try to replace the ALL( ) function to the  ALLSELECTED() function , like this:

Total count = COUNTROWS(ALLSELECTED(Undergraduate))

Because the ALL() function ignores all filtering, ALLSELECTED() function will keep your slicer's filter.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[Syndicate_Admin]

Good morning, Try to modify the measure by changing the ALL function to the ALLSELECTED function. The change has positive results as long as it is implemented in a table or matrix, however, when it is implemented in a card, the result always gives me 100%, no matter what filter or segmenter class applies to it. It should be noted that in the exercise I am handling a single table. I thank you in advance for the help.

[v-yueyunzh-msft · Accepted solution]

Hi, @Syndicate_Admin 

Good morning!

It is correct to show 100% in the card because the current filter context of the card is the filter context selected by your slicer, that is, the calculated values of [Count] and [Total count] are the same. If you want the card to display values that are not 100%, then you need to modify your metrics and add the logic you need to calculate.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[lbendlin]

The result will depend on how you have wired your data model.  Make sure that your filter choices can impact the target tables.