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 catego...
Need Help
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