Forum Discussion

Jugui01's avatar
Jugui01
New Member
3 years ago

Calculation issue because of multiple selection

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
 
 

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Jugui01
      New Member
      3 years ago

      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

       

      PeriodCountryGenderAgeDigital ContentFrequency Way to know

      October 2021

      		GermanyGirl1Podcasts2 to 3 times a weekThrough TV
      October 2021PolandGirl2Audio Book2 to 3 times a weekThrough Cinema
      October 2021PolandBoy2Audio Book2 to 3 times a weekThrough Cinema
      October 2021GermanyBoy3Mobila Game Apps2 to 3 times a weekThrough books
      October 2021GermanyGirl3PodcastsNeverThrough TV
      October 2021GermanyGirl2Audio BookNeverThrough 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's avatar
      Jugui01
      New Member
      3 years ago

      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

       

      PeriodCountryGenderAgeDigital ContentFrequency Way to know

      October 2021

      		GermanyGirl1Podcasts2 to 3 times a weekThrough TV
      October 2021PolandGirl2Audio Book2 to 3 times a weekThrough Cinema
      October 2021PolandBoy2Audio Book2 to 3 times a weekThrough Cinema
      October 2021GermanyBoy3Mobila Game Apps2 to 3 times a weekThrough books
      October 2021GermanyGirl3PodcastsNeverThrough TV
      October 2021GermanyGirl2Audio BookNeverThrough 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