topic Re: Count transactions by category in DAX Commands and Tips

Count transactions by category

Sebastián_Limon — Tue, 11 Jul 2023 17:13:33 GMT

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 ) )

Re: Count transactions by category

WinterMist — Tue, 11 Jul 2023 20:03:37 GMT

Sebastián_Limon

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

Re: Count transactions by category

WinterMist — Tue, 11 Jul 2023 21:31:06 GMT

Sebastián_Limon

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

Re: Count transactions by category

ThxAlot — Tue, 11 Jul 2023 23:54:03 GMT

grupo.pbix

A generic solution

Re: Count transactions by category

Sebastián_Limon — Fri, 14 Jul 2023 15:54:44 GMT

Thanks Nathan! it worked perfect.

Re: Count transactions by category

WinterMist — Fri, 14 Jul 2023 18:42:02 GMT

Sebastián_Limon

Glad to help. You are welcome.