Unable to get correct value for multiselect for country

[pranotid29]

Hello,

I am new to DAX functions and have the following question.

I have FY, Geo, Country, All Site, last touch channel, Visit number as my filters. My goal is to have two straight lines on the chart. One with AVG + 3* STD DEV and another with AVG - 3* STD DEV.

This is working correctly if I select one country but if I select multi countries, the calculation is wrong. Ideally, I should be able to multiselect all the filters. Below are the formulas I am using.

Std Dev = CALCULATE(

STDEV.P(Top_Sites_Data[Visits]),

ALLEXCEPT(Top_Sites_Data,Top_Sites_Data[GEO], Top_Sites_Data[Country],Top_Sites_Data[Segment], Top_Sites_Data[All Site vs Top Site], Top_Sites_Data[Last Touch Channel],Top_Sites_Data[Visit Number],Top_Sites_Data[Fiscal Year])

)

Average Visits = CALCULATE(AVERAGE(Top_Sites_Data[Visits]),

ALLEXCEPT(Top_Sites_Data,Top_Sites_Data[GEO], Top_Sites_Data[Country], Top_Sites_Data[Segment], Top_Sites_Data[All Site vs Top Site], Top_Sites_Data[Last Touch Channel],Top_Sites_Data[Visit Number],Top_Sites_Data[Fiscal Year]))

Max Visits = [Average Visits] + 3*[Std Dev]

Min Visits = if(( [Average Visits] - 3*[Std Dev]) < 0,0,( [Average Visits] - 3*[Std Dev])).

The way I understand, the table should apply the filters to data, then aggregate at week level (so that there will be 44 data points) and then calculate avg and std dev. At this point, if I select two countries, there are 88 data points instead of 44 and hence the calculation are not correct. In the second screenshot below, we can see that most of the data points are above the line, with the correct calculation , 97% of data points would lie in between the two lines.

 Thank you

[pranotid29]

@bcdobbs  I tried your suggestion and have posted the response above. Could you please have a look? Thank you.

@amitchandak tagging you here as you had helped with a similar problem. Could you please have a look as well? Thank you.

[pranotid29]

@bcdobbs Thank you for your response.

I tried using REMOVEFILTERS and VALUES as show below but the result is the same. When I select more than one country, the max and min values are not accurate.

Average Visits test =

CALCULATE(

        AVERAGE( Top_Sites_Data[Visits] ),

        REMOVEFILTERS( Top_Sites_Data ),

        VALUES( Top_Sites_Data[GEO] ),

        VALUES( Top_Sites_Data[Country] ),

        VALUES( Top_Sites_Data[Segment] ),

        VALUES( Top_Sites_Data[All Site vs Top Site] ),

        VALUES( Top_Sites_Data[Last Touch Channel] ),

        VALUES( Top_Sites_Data[Visit Number] ),

        VALUES( Top_Sites_Data[Fiscal Year] )

    )

The COUNTRY and GEO fields are created based on the segment field. Using below formulas.

Country =

if(CONTAINSSTRING(Top_Sites_Data[Segment],"Mexico"),"Mexico",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"LATAM"),"LATAM",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"Brazil"),"Brazil",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"Canada"),"Canada",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"Australia"),"Australia",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"Malaysia"),"Malaysia",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"Singapore"),"Singapore",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"India"),"India",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"China"),"China",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"Korea"),"Korea",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"UK"),"UK",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"France"),"France",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"Germany"),"Germany",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"Spain"),"Spain",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"Middle East"),"Middle East",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"Italy"),"Italy",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"Turkey"),"Turkey",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"Other"),"Other-EMEA",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"Russia"),"Russia",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"US"),"US",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"NZ") || CONTAINSSTRING(Top_Sites_Data[Segment],"New Zealand"),"NZ",

"JP")))))))))))))))))))))

GEO =

if(CONTAINSSTRING(Top_Sites_Data[Segment],"NZ") || CONTAINSSTRING(Top_Sites_Data[Segment],"New Zealand"),"APAC",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"Australia"),"APAC",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"Malaysia"),"APAC",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"Singapore"),"APAC",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"India"),"APAC",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"China"),"APAC",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"Korea"),"APAC",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"UK"),"EMEA",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"France"),"EMEA",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"Germany"),"EMEA",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"Spain"),"EMEA",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"Middle East"),"EMEA",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"Italy"),"EMEA",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"Turkey"),"EMEA",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"EMEA"),"EMEA",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"Russia"),"EMEA",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"US"),"AMER",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"Mexico"),"AMER",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"LATAM"),"AMER",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"Brazil"),"AMER",

if(CONTAINSSTRING(Top_Sites_Data[Segment],"Canada"),"AMER",

"JP")))))))))))))))))))))

 If I select one country but do multiselect with other field (e.g. Last Touch Channel), the values are not accurate. So seems like multiselect for any field is not working.

[bcdobbs]

I suspect although it's hard to test without a demo pbix file that your issue might be caused by your use of ALLEXCEPT.

Try replacing ALLEXCEPT with a combination of REMOVEFILTERS and VALUES in both measures:

Std Dev = 
	CALCULATE(
		STDEV.P( Top_Sites_Data[Visits] ),
		REMOVEFILTERS( Top_Sites_Data ),
		VALUES( Top_Sites_Data,Top_Sites_Data[GEO] ),
		VALUES( Top_Sites_Data[Country] ),
		VALUES( Top_Sites_Data[Segment] ),
		VALUES( Top_Sites_Data[All Site vs Top Site] ),
		VALUES( Top_Sites_Data[Last Touch Channel] ),
		VALUES( Top_Sites_Data[Visit Number] ),
		VALUES( Top_Sites_Data[Fiscal Year] )
	)

  Alternatively depending on how mnay columns are in the table just use REMOVEFILTERS to remove filters from the "other" columns.

If that doesn't work come back and we'll look again. I did wonder if you were having issues due to your country hierachy creating an arbitrarily shaped set.

[bcdobbs]

Explanation for why this might help is in:
Using ALLEXCEPT versus ALL and VALUES - SQLBI