Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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?
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:
"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
Solved! Go to Solution.
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.
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.
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])
Iwould recommend you to read this article about sumx.
https://powerpivotpro.com/2014/10/sum-sumx-or-calculatechoices-choices/
Best Regards
Maggie
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?
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.
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
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |