Calculation issue because of multiple selection

Jugui01 · ‎10-18-2022

Hello everyone,

Hope that you are well and that you can help me fixing my issue.

I have a table named "Consumption Frequency" with useful columns: Periode, Country, Gender, Age, device category (named Digital content), consumption frequency (named Frequency in my table), and a variable allowing to understand how we know a specific tested brand: (Through TV, Through Cinema...) (named How_did_you_come_to_know_Bing)

Using theses columns, I introduced multiple selection filters in my dashboard for each of these variable excepted the last one (how we know the brand).

I created stacked bar graph like this:

I would like to personnalize my tooltips. For doing that, I need to create a measure calculating the percentages of this graph. No problem for the numerator:

Test_numeratorr = COUNT('Consumption Frequency'[How_did_you_come_to_know_Bing])

I thought that there was no problem for calculating the denominator like this (sorry if the formula looks heavy, I am a beginner):

Test_denominator= calculate(count('Consumption Frequency'[How_did_you_come_to_know_Bing]),ALLEXCEPT('Consumption Frequency','Consumption Frequency'[Digital content],'Consumption Frequency'[Country],'Consumption Frequency'[Gender],'Consumption Frequency'[Age],'Consumption Frequency'[Periode],'Consumption Frequency'[Frequency]))

This works most of the time, but not all the time. Taking an example: If I filter on "Australia" and "Germany" in my country filter, and then I trie to see the result for the way "Through TV" for the frequency "Every day or almost", I see in my table that I don't have any any observation with the country "Germany", whereas I selected this country in my country filter. Consequently, Power BI calculates the denominator filtering only the country "Australia" in the formula, whereas I need to count the total number of people in the selected countries, selected gender, ages, periodes, digital content.

Do you see what I mean? Do you have a solution to this (I tried to duplicate the table, but no result for now)

Thanks for you

v-stephen-msft · ‎10-19-2022

Hi @Jugui01 ,

I tried to reproduced your issue but failed.

Maybe you can try

Test_denominator =
CALCULATE (
    COUNT ( 'Consumption Frequency'[How_did_you_come_to_know_Bing] ),
    FILTER (
        ALLSELECTED ( 'Consumption Frequency' ),
        [Digital content] = MAX ( 'Consumption Frequency'[Digital content] )
            && [Country] = MAX ( 'Consumption Frequency'[Country] )
            && [Gender] = MAX ( 'Consumption Frequency'[Gender] )
            && [Age] = MAX ( 'Consumption Frequency'[Age] )
            && [Periode] = MAX ( 'Consumption Frequency'[Periode] )
            && [Frequency] = MAX ( 'Consumption Frequency'[Frequency] )
    )
)

If it still doesn't work, can you please provide some sample data, and the corresponding expected results? Remember to protect privacy.

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.

Jugui01 · ‎10-19-2022

Hi Stephen Tao,

Thanks for helping me! However, the formula doesn't work.

I tried a lot of other formula, using this for instance:

Test_denominateur = calculate(countrows('Consumption Frequency'),allselected(), REMOVEFILTERS('Consumption Frequency'[How_did_you_come_to_know_Bing]))

The result is the same than my first formula.

The problem in your formula is that you apply a max function on dichotomical variables (Country, Gender (Boys, Girls)), and it seems that Power BI can't use all the elements selected in the filters using Max.

Taking an example, let's use this graph:

Here, I filtered on the Period October 2021, the country "Belgium", "Brazil" and "Chile", the gender "Girls", age filtered on 2 and 3, and digital content is "Mobile game Apps"

In a tooltip, I use the formula I provided, pointing first of all on the bar "2 to 3 times a week/Through Youtube/social media...":

The information I want in my tooltip is the total number of Girls between 2 and 3 years old in October 2021, in Belgium, Brazil and Chile, using mobile game apps 2 to 3 times a week. My formula say 10, so I should have also 10 when I point to the other way to know the brand (Through TV, Through Toys etc...), for the same frequency. However, When I see the result for the couple "2 to 3 times a week/Through TV", the result is different:

Counting in my table, The result I should have is 14. I understand that there is a problem in "Through Youtube, social media..;" because when I filter on all the elements in my table, I don't have any observation in Chile :

And Power BI automatically remove Chile from the filters when I run the measure, whereas it is not the case for "Through TV". Here are fictional data to explain what I need in these tooltips

Period	Country	Gender	Age	Digital Content	Frequency	Way to know
October 2021	Germany	Girl	1	Podcasts	2 to 3 times a week	Through TV
October 2021	Poland	Girl	2	Audio Book	2 to 3 times a week	Through Cinema
October 2021	Poland	Boy	2	Audio Book	2 to 3 times a week	Through Cinema
October 2021	Germany	Boy	3	Mobila Game Apps	2 to 3 times a week	Through books
October 2021	Germany	Girl	3	Podcasts	Never	Through TV
October 2021	Germany	Girl	2	Audio Book	Never	Through Cinema

Using the same type of graph (stacked bar plot), I need, filtering on [Germany and Poland, Girl, 1 and 2 years old, no filter for digital content], to return 2 when I want to see my tooltips for the frequency "2 to 3 times a week", and 1 for the frequency "Never".

I hope that my explanations are clear.

Best,

Jugui01

Jugui01 · ‎10-19-2022