Count Distinct for positive sales

SkyIsTheLimit — Sat, 16 Nov 2024 17:34:05 GMT

Hi all,

I'm currently working on my APP, which consists on a database with these fields:

-Business Line (dimension)
-OrderID (dimension)
-EuropeSales (value)
-AmericaSales (value)
-AsiaSales (value)
-AfricaSales (value)

Each order ID matches a unique region and a unique business line, so the content of that database would be something like this:

Business Line OrderID EuropeSales AmericaSales AsiaSales AfricaSales
Soccer 3772908 322.33 0.00 0.00 0.00
Basketball 3763393 0.00 38.99 0.00 0.00
Soccer 3473033 0.00 0.00 255.00 0.00
Tennis 3433339 0.00 0.00 15.30 0.00
Tennis 3429392 0.00 0.00 0.00 76.98

Now, I want to create a pivot table so that it shows the amount of different orders for each business line and each region, taking into account the sales values. The content would be like this:

Business Line EuropeOrders AmericaOrders AsiaOrders AfricaOrders
Soccer 125 12 3 0
Basketball 42 16 48 2
Tennis 33 2 1 13

I'm trying to use the following expression, but it isn't working properly as it shows the same number, like this:

Business Line EuropeOrders AmericaOrders AsiaOrders AfricaOrders
Soccer 140 140 140 140
Basketball 108 108 108 108
Tennis 49 49 49 49

The expressions I'm using are these:

EuropeOrders = count({<[EuropeSales]={">0"}>}distinct([OrderID]))
AmericaOrders = count({<[AmericaSales]={">0"}>}distinct([OrderID]))
AsiaOrders = count({<[AsiaSales]={">0"}>}distinct([OrderID]))
AfricaOrders = count({<[AfricaSales]={">0"}>}distinct([OrderID]))

Which is the mistake? I can't find it.

Thanks in advance!

topic Count Distinct for positive sales in App Development

Count Distinct for positive sales