Forum Discussion
Count transactions by category
Hi, I need help with a measurement. I need to count how many transactions certain amounts of product categories contain.
The categories can be three Calzado, Ropa and Accesorios.
For example, I want to know how many transactions contain Ropa and Accesorios.
Here, I leave an example table. where seven transactions contains "Ropa and Accesorios"
folionum grupo
7550286 Ropa
7550286 Ropa
7550287 Ropa
7550287 Ropa
7550288 Ropa
7550288 Ropa
7550288 Calzado
7550289 Ropa
7550290 Ropa
7550290 Accesorios
7550290 Accesorios
7550291 Accesorios
7550292 Ropa
7550292 Ropa
7550292 Accesorios
7550293 Ropa
7550293 Accesorios
7550294 Ropa
7550294 Calzado
7550295 Ropa
7550295 Accesorios
7550296 Accesorios
7550297 Ropa
7550297 Ropa
7550297 Accesorios
7550298 Ropa
7550298 Accesorios
7550299 Accesorios
7550299 Ropa
7550299 Ropa
7550299 Ropa
7550300 Ropa
7550300 Accesorios
I am using the following measure, but it doesn't work
Transacciones de Productos =
CALCULATE(
COUNTAX(
FILTER(
VALUES('Base Limonada 23'[folionum]);
CALCULATE(
DISTINCTCOUNT('Base Limonada 23'[grupo]);
'Base Limonada 23'[grupo] IN {"Ropa"; "Accesorios"}
) = 2
);
1
)
)
OK, for some reason that I don't fully understand, I had to create a 3rd measure to make the total work correctly.
(I believe it has to do with context transition.)
Anyway, here are the 3 measures:
Results:
Hopefully this is helpful to you.
Regards,
Nathan
5 Replies
I'm about 90% there. Should have the last bit worked out shortly.
As you can see from the screenshots, the 2 measures are working properly within the rows of the table visual.
However, the total of 15 is not correct. The correct total should be 8.
I believe this has to do with additive vs. non-additive measures.
I'll get back to you soon.
Regards,
Nathan
OK, for some reason that I don't fully understand, I had to create a 3rd measure to make the total work correctly.
(I believe it has to do with context transition.)
Anyway, here are the 3 measures:
Results:
Hopefully this is helpful to you.
Regards,
Nathan
Thanks Nathan! it worked perfect.
