Switch and True

[reyesdjr]

Hello, 

I am using this: 

Category =
SWITCH(
TRUE(),
'Category'[lineamount] < 5000.01, "A. <5,000.01",
'Category'[lineamount] >= 5000.00 && 'Category'[lineamount] < 10000.01, "B. >5,000.00 - <10,000.01",
'Category'[lineamount] >= 10000.00 && 'Category'[lineamount] < 50000.01, "C. >10,000.00 - <50,000.01",
'Category'[lineamount] >= 50000.00, "D. >50,000.00",
"Uncategorized"
) 

but its not reading it correctly. When I extracted the data from PowerBi to confirm there are amount that are not aligning. For example. See spreadsheet below, cant seem to attach the entire spreadsheet. Thank you for helping!

$39,092.61	<5,000.01
$36,001.15	<5,000.01
$27,819.80	<5,000.01
$23,626.00	<5,000.01
$22,950.02	<5,000.01
$21,411.56	<5,000.01
$19,548.57	<5,000.01
$19,500.00	<5,000.01
$18,450.00	<5,000.01
$15,749.98	<5,000.01
$13,628.40	<5,000.01
$12,727.00	<5,000.01
$12,509.60	<5,000.01
$11,611.00	<5,000.01
$10,745.00	<5,000.01
$10,246.60	<5,000.01
$10,000.00	<5,000.01
$9,998.70	<5,000.01
$9,957.60	<5,000.01
$9,709.00	<5,000.01
$9,512.52	<5,000.01
$9,323.00	<5,000.01
$9,282.42	<5,000.01
$9,127.59	<5,000.01
$9,002.59	<5,000.01
$8,690.00	<5,000.01
$8,630.02	<5,000.01
$8,583.51	<5,000.01
$8,580.00	<5,000.01
$8,529.00	<5,000.01
$7,796.65	<5,000.01
$7,507.53	<5,000.01
$7,465.00	<5,000.01
$7,309.28	<5,000.01
$7,300.00	<5,000.01
$7,223.78	<5,000.01
$7,172.00	<5,000.01
$7,167.50	<5,000.01
$7,126.00	<5,000.01
$7,006.00	<5,000.01
$6,981.25	<5,000.01
$6,894.14	<5,000.01
$6,828.30	<5,000.01
$6,792.35	<5,000.01
$6,695.52	<5,000.01
$6,620.00	<5,000.01
$6,562.78	<5,000.01
$6,552.00	<5,000.01
$6,440.00	<5,000.01
$6,433.04	<5,000.01
$6,294.98	<5,000.01
$6,260.00	<5,000.01
$6,207.50	<5,000.01
$5,950.00	<5,000.01
$5,769.39	<5,000.01
$5,625.96	<5,000.01
$5,600.00	<5,000.01
$5,578.69	<5,000.01
$5,551.65	<5,000.01
$5,530.00	<5,000.01
$5,496.17	<5,000.01
$5,460.16	<5,000.01
$5,427.84	<5,000.01
$5,400.00	<5,000.01
$5,393.20	<5,000.01
$5,355.00	<5,000.01
$5,327.66	<5,000.01
$5,238.59	<5,000.01
$5,218.00	<5,000.01
$5,158.80	<5,000.01
$5,142.52	<5,000.01
$5,047.95	<5,000.01

[v-zhouwen-msft]

Hi reyesdjr ,

Is your problem solved? If you encounter new problems, please raise them here.

Best Regards,
Wenbin Zhou

[reyesdjr]

I think I found the reason, I am using a line amount $ value per order, so for example there are 3 line amounts in an order, when I use the order # to count the category it will not be more than since there are duplicate order numbers. 

Thank you so much for your help! appreciate it a lot!

[Ashish_Mathur]

Hi,

Your calculated column formula works just fine for me

[lbendlin]

looks like you created a measure.  you need to create a calculated column

Category = 
SWITCH(
TRUE(),
[lineamount] < 5000.01, "A. <5,000.01",
[lineamount] < 10000.01, "B. >5,000.00 - <10,000.01",
[lineamount] < 50000.01, "C. >10,000.00 - <50,000.01",
 "D. >50,000.00"
) 

[reyesdjr]

Thank you so much for replying, I created a caculated column: 

% of PO's created by value =
SWITCH(
TRUE(),
'SCM POAndItemsByProcurementCategory'[lineamount] < 5000.01, "A. <5,000.01",
'SCM POAndItemsByProcurementCategory'[lineamount] < 10000.01, "B. >5,000.00 - <10,000.01",
'SCM POAndItemsByProcurementCategory'[lineamount] < 50000.01, "C. >10,000.00 - <50,000.01",
'SCM POAndItemsByProcurementCategory'[lineamount] > 50000.01, "D. >50,000.00"
)

I did change it based from what you suggested but its still showing me this:

Thank you again!

[muhammad_786_1 · Accepted solution]

I hope it can work for you. Create a calculated column like that:

Category =
SWITCH(
TRUE(),
'Category'[lineamount] >= 50000.00, "D. >50,000.00",
'Category'[lineamount] >= 10000.00 && 'Category'[lineamount] < 50000.01, "C. >10,000.00 - <50,000.01",
'Category'[lineamount] >= 5000.00 && 'Category'[lineamount] < 10000.01, "B. >5,000.00 - <10,000.01",
'Category'[lineamount] < 5000.01, "A. <5,000.01",
"Uncategorized"
)

In SWITCH(TRUE()) statement, the order of conditions is critical because it evaluates sequentially.
That's why, you should evaluate the higher-value conditions first before moving to lower-value condition.

Best Regards,
Muhammad Yousaf

If this post helps, then please consider "Accept it as the solution" to help the other members find it more quickly.

Follow on LinkedIn

[reyesdjr]

Does it matter if the data I provided is just for 3 months? I did try the calculated column you provided and count of the category changed, just that if I do it manually in excel the count is different. Does it also matter if there are multiple rows for the column that I use as a count?

Thank you!