How to Count Dynamic Measures based on Multiple Conditions/If statements - Dynamic Bucketing?

[villegbr]

Hello All!

I have come across a fork in the road. I'm attempting to count the total number of measure results. The measure results are essentially categories which are based on 2 other measures. For example, my categories are as follows : 

Category = SWITCH( TRUE(),
[IN USA] <1, "OUT OF STUCK:URGENT PRODUCTION",
[IN USA] <=6 , "REVIEW CUSTOMER ORDERS:URGENT PRODUCTION",
AND ([WEEKS OF INVENTORY]<18, [IN USA]<12), "RISK:URGENT PRODUCTION",
AND ([WEEKS OF INVENTORY]<18, [IN USA]>12), "PRODUCTION",
AND ([WEEKS OF INVENTORY]>18, [IN USA]>12),"IN STOCK", BLANK()
)

 [IN USA] AND [WEEKS OF INVENTORY] are both calculated measures as well. 

I was able to create the following dynamic bucket (based on this video by the brilliant Amit https://www.youtube.com/watch?v=CuczXPj0N-k&ab_channel=AmitChandak ) but unfortunately the numbers are not completely accurate because I am unsure as to how to create a bucket with multiple if statements withthn in .  I created a new table based on the following limits called Table 2. 

START LIMIT	END LIMIT	CATEGORY
-1000	0	OUT OF STOCK
.01	6	REVIEW CUSTOMER ORDERS:PRODUCTION
6.01	12	RISK:URGENT PRODUCTION
12.01	18	PRODUCTION
18.01	1000	IN STOCK

The code for the bucketing : 

CATEGORY BUCKET = COUNTX( FILTER ( VALUES ( 'TABLE'[ITEM NO_]), [WEEKS OF INVENTORY] >= MIN('TABLE (2)'[START LIMIT]) && [WEEKS OF INVENTORY]<MAX('TABLE 2'[END LIMIT])), 'TABLE'[ITEM NO_])

The data should have showed 

IN STOCK	9
OUT OF STOCK:URGENT PRODUCTION	19
PRODUCTION	11
REVIEW CUSTOMER ORDERS:URGENT PRODUCITON	24
RISK:URGENT PRODUCTION	21
GRAND TOTAL	84

The results I get are : 

CATEGORY	CATEGORY BUCKET
IN STOCK	10
PRODUCTION	10
REVIEW CUSTOMER ORDER:URGENT PRODUCTION	17
RISK:URGENT PRODUCTION	27

Any help will be greatly appreciated! 😃

[technolog · Accepted solution]

Firstly, the SWITCH statement you provided is creating categories based on the values of two measures: [IN USA] and [WEEKS OF INVENTORY]. The logic seems clear, but there's a potential overlap in the conditions. For instance, if [IN USA] is 10 and [WEEKS OF INVENTORY] is 10, it will fall into the "RISK:URGENT PRODUCTION" category. But if [IN USA] is 15 and [WEEKS OF INVENTORY] is 10, it will fall into the "PRODUCTION" category.

Now, the bucketing logic you've shown is only considering the [WEEKS OF INVENTORY] measure and not the [IN USA] measure. This is likely the reason for the discrepancy in the results.

To fix this, you need to adjust the bucketing logic to consider both measures. Here's a suggestion:

Modify the 'Table 2' to include columns for both [IN USA] and [WEEKS OF INVENTORY] start and end limits. This will allow you to define the ranges for both measures for each category.

Adjust the bucketing logic to filter based on both measures. Here's a rough idea:

CATEGORY BUCKET =
COUNTX(
FILTER (
VALUES ( 'TABLE'[ITEM NO_]),
[WEEKS OF INVENTORY] >= MIN('TABLE 2'[WEEKS START LIMIT]) &&
[WEEKS OF INVENTORY] < MAX('TABLE 2'[WEEKS END LIMIT]) &&
[IN USA] >= MIN('TABLE 2'[IN USA START LIMIT]) &&
[IN USA] < MAX('TABLE 2'[IN USA END LIMIT])
),
'TABLE'[ITEM NO_]
)
Ensure that the categories in 'Table 2' match the categories from your SWITCH statement. This will ensure that the bucketing logic is consistent with the category assignment logic.
By doing this, you're ensuring that both measures are considered when bucketing the items into categories. This should help you get results that are consistent with the SWITCH statement logic.