Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

ALLEXCEPT differents results when using SUMX or CALCULATE- SLICER problems

Hello, im spanish.
I have a question/problem using ALLEXCEPT in SUMX and CALCULATE and SLICER because i have diferent results according to use one or other slicer.
I have 2 Tables: "Ventas" (Sales) and "Calendario" (Calendar), with 1:Many relationship from Calendar[fecha] (date) to Sales[fecha_carga] (sales_date). 
I use 2 measures:  (I sum all units sold)
calc=
CALCULATE(SUM(Ventas[unidades_vendidas]);ALLEXCEPT(Ventas;Ventas[fecha_carga]))
sumx = SUMX(ALLEXCEPT(Ventas;Ventas[fecha_carga]); Ventas[unidades_vendidas])
2 slicer: Calendar Date[Date] slicer and FECHA VENTAS[fecha_carga] slicer.
So when i pick a date from Sales[date_sale] slicer, measure "sumx" dont change but measure "calc" change value according to the date.bi1.jpg
1)The first question is why does ALLEXCEPT do that?


2)Then another question came 
up to me cause(logically) Calendar[Date] slicer affects Sales[date_sales] cause 1:Many relationship. So if rows from Sales are affected (or column) why no are affected "calc" measure? b3.jpg

For example when i select a range of dates, "fecha_carga" change according to CALENDAR TABLE Slicer but any measure change according to the selected date.

3) Then i tried a simple measure like: simple_calc =CALCULATE(SUM(Ventas[unidades_vendidas); Ventas[fecha_carga]) and for my surprise i have differents results:
b4.jpg

"CALC" is "simple_calc" measure and its value is changed by "CALENDAR DATE" slicer and "CALC_ALLEX", its value changed by "FECHA VENTAS" Slicer.¿¿WHY??
And all this questions it's because I'm trying to calculate the percentage of country sales in a range of dates, not from this year
4) Of all the sales in this year, 121 millions in my case, Spain sales are 30 %. I calculate it using CALCULATE and ALL to divide Spain sales from total sales:
CALCULATE(SUM(Ventas[unidades_vendidas])) /
SUMX(ALL(Ventas);Ventas[fecha_carga]);
It is ok, but when i use this measure:
CALCULATE(SUM(Ventas[unidades_vendidas])) /
SUMX(ALLEXCEPT(Ventas; Ventas[fecha_carga]; Ventas[month]; Ventas[year]); Ventas[unidades_vendidas])
It doesnt works, i think that's not the way. SO WHY CAN I DO IT??
Thanks very much

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I have the solution for the four scenario , that it was mi principal doubt, for obtain the country sales percentaje of a range date, not the percentage of all sales, i did a measure using ALL in calculate =>  

CALCULATE( SUM(Ventas[unidades_vendidas])) / CALCULATE( SUM(Ventas[unidades_vendidas]); ALL(Ventas[country]))
To easy.
The other scenearios still i dont know why that happens. 

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

I have the solution for the four scenario , that it was mi principal doubt, for obtain the country sales percentaje of a range date, not the percentage of all sales, i did a measure using ALL in calculate =>  

CALCULATE( SUM(Ventas[unidades_vendidas])) / CALCULATE( SUM(Ventas[unidades_vendidas]); ALL(Ventas[country]))
To easy.
The other scenearios still i dont know why that happens. 

v-juanli-msft
Community Support
Community Support

Hi @Anonymous

For the first scenario, after i tested, it seems the result of [sumx] is not the same as [calc]'s even without date slicer.

But a little modification can make difference

sumx 2 = SUMX(FILTER(ALL(Ventas),[echa_carga]=MAX([echa_carga])),Ventas[unidades_vendidas])

4.png

 

Iwould recommend you to read this article about sumx.

https://powerpivotpro.com/2014/10/sum-sumx-or-calculatechoices-choices/ 

 

Best Regards

Maggie

Anonymous
Not applicable

Hi Maggie, thanks for reply.
But in your case i dont understand what does [echa_carga]=MAX[echa_carga] to get the result.
MAX return blank if columns isnt number, so can you explain me how that filter works if SUMX evaluate each row one by one?

Anonymous
Not applicable

What the measure is doing in this case is it is summing hte unidades_vendidas column over a filtered version of the table where echa_carga is equal to the maximum value of echa_carga.

 

It works as if you use MAX on a text field it will sort alphabetically. The DAX documentation is a bit strange in this regard in that it is incorrect. It is the same as for COUNT/COUNTA if you want to find the max of all data types you need to use MAXA however in Power BI the only data type that MAX cannot work with is Logicals.

Anonymous
Not applicable

But in my mind I cant imagine how "version of the table where echa_carga is equal to the maximum value of echa_carga" groups the Table in 2 groups. When echa_carga = a, MAX(echa_carga) = a. And when echa_carga = b, MAX(echa_carga) = a, so how it let SUMX sums rows for each group separately?

Hi @Anonymous

If you use [echa_carga] =MAX([echa_carga]) in a measure withoutthe limited condition, MAX([echa_carga]) refers to a current row.

 

Best Regards

Maggie

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.